SQL Server Performance

Log space used.

Discussion in 'Contribute Your SQL Server Scripts' started by Haywood, Apr 21, 2004.

  1. Haywood New Member

    I'll post more scripts of this type if this one cuts the mustard with other DBA's.<br /><br />Compile in master or your own DBA database. This script shows the total log space consumed for a given database and day (INT). It will also list the sizes of the individual log backups.<br /><br /><br />DrHyde on #SQL, Efnet.<br /><br /><br /><br />CREATE PROCEDURE usp_LogSnapShot (@Days INT, @DBName VARCHAR(20)) -- usp_LogSnapShot 0, 'SomeDB' <br /><br />AS<br /><br />/******************************************************************************<br />**Process: usp_LogSnapshot<br />**<br />** Reads logspace consumption for a given database & day. <br />** <br />** <br />**<br />**Author: G. Rayburn -- haywood@phantom.kirenet.com<br />**<br />**Date: 1/20/2004<br />**<br />*******************************************************************************<br />**Modification History<br />*******************************************************************************<br />**<br />**Initial Creation: 1/20/2004 G. Rayburn<br />**<br />*******************************************************************************/<br /><br />-- -- DEBUG<br />-- DECLARE @Days INT,<br />-- @DBName VARCHAR(20)<br />-- -- DEBUG<br />-- SELECT @Days = -1,<br />-- @DBName = 'IM2K_Billing'<br /><br /><br />SET NOCOUNT ON<br /><br />DECLARE @TotalUsed VARCHAR(15)<br /><br />SELECT @TotalUsed = ( SELECT SUM(CONVERT(NUMERIC(8,2),s.backup_size / 1024 / 1024))<br />FROM msdb.dbo.backupset AS s<br />JOIN msdb.dbo.backupmediafamily AS m<br />ON s.media_set_id = m.media_set_id<br />WHERE database_name = @DBName<br /> AND s.type = 'L' -- LOG BACKUP 'D' = DATABASE BACKUP<br /> AND CONVERT(VarChar(11),backup_start_date) = CONVERT(VarChar(11),DATEADD(d,@Days,getdate())) )<br /><br /><br />PRINT @DBName + ' Log space for ' + CONVERT(VarChar(11),DATEADD(d,@DAYS,getdate())) + '.'<br />PRINT ''<br />PRINT 'Total logspace consumed: ' + @TotalUsed + ' MB'<br />PRINT ''<br /><br /><br /><br />PRINT 'Log space for ' + @DBNAME + ' on ' +CONVERT(VarChar(11),DATEADD(d,@DAYS,getdate()))<br />PRINT ''<br /><br />SELECT s.Position AS [Backup Position],<br />LEFT(s.[Description],36) AS [Backup Description],<br />CONVERT(VarChar(<img src='/community/emoticons/emotion-11.gif' alt='8)' />,backup_start_date,<img src='/community/emoticons/emotion-11.gif' alt='8)' /> AS [Start Time],<br />CONVERT(VarChar(<img src='/community/emoticons/emotion-11.gif' alt='8)' />,backup_finish_date,<img src='/community/emoticons/emotion-11.gif' alt='8)' /> AS [Finish Time],<br />CONVERT(NUMERIC(8,3),s.backup_size / 1024 / 1024) AS [Backup Size in MB]<br />FROM msdb.dbo.backupset AS s<br /> JOIN msdb.dbo.backupmediafamily AS m<br /> ON s.media_set_id = m.media_set_id<br />WHERE database_name = @DBName<br />AND s.type = 'L' -- LOG BACKUP 'D' = DATABASE BACKUP<br />AND CONVERT(VarChar(11),backup_start_date) = CONVERT(VarChar(11),DATEADD(d,@Days,getdate())) <br /><br />ORDER BY 3 ASC<br /><br />GO<br />SET QUOTED_IDENTIFIER OFF <br />GO<br />SET ANSI_NULLS ON <br />GO<br /><br /><br /><br />
  2. gaurav_bindlish New Member

    MSDB..BACKUPSET stores the history of all the backups (database and log).

    As far as I understand, this will only give the details about the last log backup and is not a real time based data. Is that what you intended to do?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  3. Haywood New Member

    quote:Originally posted by gaurav_bindlish

    MSDB..BACKUPSET stores the history of all the backups (database and log).

    As far as I understand, this will only give the details about the last log backup and is not a real time based data. Is that what you intended to do?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.


    That's what the @Days parameter is for. It will show you all log backups for a database on a given day. The format will be a bit wierd because of the message board, but you'll get the idea.

    Here's a sample output:


    IM2K_Billing Log space for Apr 22 2004.

    Total logspace consumed: 1369.81 MB

    Log space for IM2K_Billing on Apr 22 2004

    Backup Position Backup Description Start Time Finish Time Backup Size in MB
    --------------- ------------------------------------ ---------- ----------- -----------------
    2 Appended log backup. 00:00:03 00:00:03 .153
    3 Appended log backup. 01:00:03 01:00:04 1.091
    4 Appended log backup. 02:00:04 02:00:11 110.719
    5 Appended log backup. 03:00:03 03:00:21 338.741
    6 Appended log backup. 04:00:03 04:00:05 20.529
    7 Appended log backup. 05:00:04 05:00:14 119.033
    8 Appended log backup. 06:00:07 06:00:09 10.968
    9 Appended log backup. 07:00:05 07:00:08 36.281
    10 Appended log backup. 08:00:05 08:00:09 68.156
    11 Appended log backup. 09:00:06 09:00:13 92.281
    12 Appended log backup. 10:00:05 10:00:13 108.533
    13 Appended log backup. 11:00:05 11:00:16 109.538
    14 Appended log backup. 12:00:05 12:00:12 119.725
    15 Appended log backup. 13:00:05 13:00:12 116.658
    16 Appended log backup. 14:00:05 14:00:12 117.409

Share This Page