locked
Need a script for log space monitoring RRS feed

  • Question

  • Hi All,

    Kindly help me out, i need a script which automatically trigger E-Mail alert, once log space comes to 80% of used space.

    I need to automate this same process in SQL 2000 & SQL 2005 SQL servers in different domains.


    RM Thirunavukkarasu MCP, MCITP, ITIL V3 |Click "Propose As Answer" if solution is helpful to you|http://thiruna.blog.com. Thiru - MS SQL World
    • Moved by Tom Phillips Thursday, February 24, 2011 1:38 PM TSQL question (From:SQL Server Database Engine)
    Thursday, February 24, 2011 11:24 AM

Answers

  • /*============================================================================
     File: SQLskillsLogInfo.sql

     Summary: This script creates a system-wide SP SQLskillsLogInfo that
     displays log usage information as well as the number of VLFs in the log
     file for all databases on an instance

     
    ============================================================================*/

    -- Create the function in msdb
    --
    USE msdb;
    GO

    IF EXISTS (SELECT * FROM sysobjects WHERE NAME = 'sp_SQLskillsAllLogInfo')
     DROP PROCEDURE sp_SQLskillsAllLogInfo;
    GO

    -- This SP reports log info for all databases on an instance:
    --
    -- EXEC sp_SQLskillsAllLogInfo;
    -- GO
    --
    -- DB ID  Recovery Model  Log Size (MB)  Log Used (%)   Log File Count  VLF Count
    -- ------ --------------- -------------- -------------- --------------- ----------
    -- 1      SIMPLE          6.12           34.42          1               22
    -- 2      SIMPLE          0.49           75.60          1               2
    -- 3      FULL            14.68          97.90          1               51
    -- 4      SIMPLE          2.49           45.92          1               10
    -- 11     SIMPLE          1.99           33.55          1               4
    -- 12     FULL            359.99         77.84          1               96
    -- 13     FULL            0.48           45.67          1               2
    --
    CREATE PROCEDURE sp_SQLskillsAllLogInfo
    AS
    BEGIN
     SET NOCOUNT ON;

     -- Create the temp tables
     --
     IF EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE NAME = 'SQLskillsAllInfo')
      DROP TABLE msdb.dbo.SQLskillsAllInfo;

     IF EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE NAME = 'SQLskillsLogSpace')
      DROP TABLE msdb.dbo.SQLskillsLogSpace;

     IF EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE NAME = 'SQLskillsLogInfo')
      DROP TABLE msdb.dbo.SQLskillsLogInfo;

     IF EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE NAME = 'SQLskillsSYSDB')
      DROP TABLE msdb.dbo.SQLskillsSYSDB;

     CREATE TABLE msdb.dbo.SQLskillsAllInfo (
      [DatabaseName] VARCHAR (128),
      [LogSize]  FLOAT,
      [LogUsed]  FLOAT,
      [FileCount]  INT,
      [VLFCount]  INT,
      [Rebuilds]  INT,
      [RecModel]  VARCHAR (15));

     CREATE TABLE msdb.dbo.SQLskillsLogSpace (
      [DatabaseName] VARCHAR (128),
      [LogSize]  FLOAT,
      [LogUsed]  FLOAT,
      [Status]  INT);

     CREATE TABLE msdb.dbo.SQLskillsLogInfo (
      [FileId]  INT,
      [FileSize]  BIGINT,
      [StartOffset] BIGINT,
      [FSeqNo]  INT,
      [Status]  INT,
      [Parity]  TINYINT,
      [LSN]   VARCHAR (100));  

     -- Get the DBCC SQLPERF (LOGSPACE) info and transfer to
     -- the aggregate table
     --
     INSERT INTO msdb.dbo.SQLskillsLogSpace
     EXEC ('DBCC SQLPERF (LOGSPACE)');

     INSERT msdb.dbo.SQLskillsAllInfo (
      [DatabaseName], [LogSize], [LogUsed], [VLFCount])
     SELECT * FROM msdb.dbo.SQLskillsLogSpace;

     -- Declare variables
     --
     DECLARE @VLFs  INT;
     DECLARE @FCount  INT;
     DECLARE @status  INT;
     DECLARE @DbName  sysname;
     DECLARE @RMDesc  VARCHAR (128);

     -- Take a copy of the master.dbo.sysdatabases output as
     -- using a cursor directly on it was producing unpredictable
     -- results
     --
     SELECT * INTO msdb.dbo.SQLskillsSysDb
     FROM master.dbo.sysdatabases;

     -- Declare and open the cursor - this is easier
     -- than trying to use sp_MSforeachdb
     --
     DECLARE databases CURSOR FOR
      SELECT [name], [status] FROM msdb.dbo.SQLskillsSysDb;

     OPEN databases;

     -- Loop through all online databases
     --
     FETCH NEXT FROM databases INTO @DbName, @status;

     WHILE @@FETCH_STATUS = 0
     BEGIN
      INSERT INTO msdb.dbo.SQLskillsLogInfo
      EXEC ('DBCC LOGINFO (''' + @DbName + ''') WITH NO_INFOMSGS');

      select @FCount = COUNT (*)
      FROM master.dbo.sysaltfiles
      WHERE [dbid] = DB_ID (@DbName)
      AND [groupid] = 0;

      SELECT @VLFs = COUNT (*)
      FROM msdb.dbo.SQLskillsLogInfo;

      IF (@status & 4 = 4)
       SELECT @RMDesc = 'BULK_LOGGED';
      ELSE IF (@status & 8 = 8)
       SELECT @RMDesc = 'SIMPLE';
      ELSE
       SELECT @RMDesc = 'FULL';

      UPDATE msdb.dbo.SQLskillsAllInfo
      SET [FileCount] = @FCount,
       [VLFCount] = @VLFs,
       [RecModel] = @RMDesc
      WHERE [DatabaseName] = @DbName;

      TRUNCATE TABLE msdb.dbo.SQLskillsLogInfo;

      FETCH NEXT FROM databases INTO @DbName, @status;
     END

     -- Close and deallocate the cursor
     --
     CLOSE databases;
     DEALLOCATE databases;

     -- Output the results
     --
     SELECT
      DB_NAME(DB_ID ([DatabaseName])) AS [DB ID],
      [RecModel] AS [Recovery Model],
      ROUND ([LogSize], 2) AS [Log Size (MB)],
      ROUND ([LogUsed], 2) AS [Log Used (%)],
      [FileCount] AS [Log File Count],
      [VLFCount] AS [VLF Count]
     FROM msdb.dbo.SQLskillsAllInfo;

     -- Drop the temp tables
     --
     DROP TABLE msdb.dbo.SQLskillsLogSpace;
     DROP TABLE msdb.dbo.SQLskillsLogInfo;
     DROP TABLE msdb.dbo.SQLskillsAllInfo;
     DROP TABLE msdb.dbo.SQLskillsSysDb;
    END;
    GO

    -- Run the proc
    --
    EXEC sp_SQLskillsAllLogInfo;
    GO


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, February 24, 2011 11:46 AM
    Answerer

All replies

  • hello,

    You have to create an SQL Server Alert based of Performance Counter Value.

    You will also be able to use this alert to start a Job that will perform backup Log files to prevent Log file to growth indifently.

    if you understand french language you can use this post to perform this action : http://blog.piservices.fr/category/SQL-Server.aspx

    Best Reagrds
    J.K


    J.K
    Thursday, February 24, 2011 11:37 AM
  • /*============================================================================
     File: SQLskillsLogInfo.sql

     Summary: This script creates a system-wide SP SQLskillsLogInfo that
     displays log usage information as well as the number of VLFs in the log
     file for all databases on an instance

     
    ============================================================================*/

    -- Create the function in msdb
    --
    USE msdb;
    GO

    IF EXISTS (SELECT * FROM sysobjects WHERE NAME = 'sp_SQLskillsAllLogInfo')
     DROP PROCEDURE sp_SQLskillsAllLogInfo;
    GO

    -- This SP reports log info for all databases on an instance:
    --
    -- EXEC sp_SQLskillsAllLogInfo;
    -- GO
    --
    -- DB ID  Recovery Model  Log Size (MB)  Log Used (%)   Log File Count  VLF Count
    -- ------ --------------- -------------- -------------- --------------- ----------
    -- 1      SIMPLE          6.12           34.42          1               22
    -- 2      SIMPLE          0.49           75.60          1               2
    -- 3      FULL            14.68          97.90          1               51
    -- 4      SIMPLE          2.49           45.92          1               10
    -- 11     SIMPLE          1.99           33.55          1               4
    -- 12     FULL            359.99         77.84          1               96
    -- 13     FULL            0.48           45.67          1               2
    --
    CREATE PROCEDURE sp_SQLskillsAllLogInfo
    AS
    BEGIN
     SET NOCOUNT ON;

     -- Create the temp tables
     --
     IF EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE NAME = 'SQLskillsAllInfo')
      DROP TABLE msdb.dbo.SQLskillsAllInfo;

     IF EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE NAME = 'SQLskillsLogSpace')
      DROP TABLE msdb.dbo.SQLskillsLogSpace;

     IF EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE NAME = 'SQLskillsLogInfo')
      DROP TABLE msdb.dbo.SQLskillsLogInfo;

     IF EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE NAME = 'SQLskillsSYSDB')
      DROP TABLE msdb.dbo.SQLskillsSYSDB;

     CREATE TABLE msdb.dbo.SQLskillsAllInfo (
      [DatabaseName] VARCHAR (128),
      [LogSize]  FLOAT,
      [LogUsed]  FLOAT,
      [FileCount]  INT,
      [VLFCount]  INT,
      [Rebuilds]  INT,
      [RecModel]  VARCHAR (15));

     CREATE TABLE msdb.dbo.SQLskillsLogSpace (
      [DatabaseName] VARCHAR (128),
      [LogSize]  FLOAT,
      [LogUsed]  FLOAT,
      [Status]  INT);

     CREATE TABLE msdb.dbo.SQLskillsLogInfo (
      [FileId]  INT,
      [FileSize]  BIGINT,
      [StartOffset] BIGINT,
      [FSeqNo]  INT,
      [Status]  INT,
      [Parity]  TINYINT,
      [LSN]   VARCHAR (100));  

     -- Get the DBCC SQLPERF (LOGSPACE) info and transfer to
     -- the aggregate table
     --
     INSERT INTO msdb.dbo.SQLskillsLogSpace
     EXEC ('DBCC SQLPERF (LOGSPACE)');

     INSERT msdb.dbo.SQLskillsAllInfo (
      [DatabaseName], [LogSize], [LogUsed], [VLFCount])
     SELECT * FROM msdb.dbo.SQLskillsLogSpace;

     -- Declare variables
     --
     DECLARE @VLFs  INT;
     DECLARE @FCount  INT;
     DECLARE @status  INT;
     DECLARE @DbName  sysname;
     DECLARE @RMDesc  VARCHAR (128);

     -- Take a copy of the master.dbo.sysdatabases output as
     -- using a cursor directly on it was producing unpredictable
     -- results
     --
     SELECT * INTO msdb.dbo.SQLskillsSysDb
     FROM master.dbo.sysdatabases;

     -- Declare and open the cursor - this is easier
     -- than trying to use sp_MSforeachdb
     --
     DECLARE databases CURSOR FOR
      SELECT [name], [status] FROM msdb.dbo.SQLskillsSysDb;

     OPEN databases;

     -- Loop through all online databases
     --
     FETCH NEXT FROM databases INTO @DbName, @status;

     WHILE @@FETCH_STATUS = 0
     BEGIN
      INSERT INTO msdb.dbo.SQLskillsLogInfo
      EXEC ('DBCC LOGINFO (''' + @DbName + ''') WITH NO_INFOMSGS');

      select @FCount = COUNT (*)
      FROM master.dbo.sysaltfiles
      WHERE [dbid] = DB_ID (@DbName)
      AND [groupid] = 0;

      SELECT @VLFs = COUNT (*)
      FROM msdb.dbo.SQLskillsLogInfo;

      IF (@status & 4 = 4)
       SELECT @RMDesc = 'BULK_LOGGED';
      ELSE IF (@status & 8 = 8)
       SELECT @RMDesc = 'SIMPLE';
      ELSE
       SELECT @RMDesc = 'FULL';

      UPDATE msdb.dbo.SQLskillsAllInfo
      SET [FileCount] = @FCount,
       [VLFCount] = @VLFs,
       [RecModel] = @RMDesc
      WHERE [DatabaseName] = @DbName;

      TRUNCATE TABLE msdb.dbo.SQLskillsLogInfo;

      FETCH NEXT FROM databases INTO @DbName, @status;
     END

     -- Close and deallocate the cursor
     --
     CLOSE databases;
     DEALLOCATE databases;

     -- Output the results
     --
     SELECT
      DB_NAME(DB_ID ([DatabaseName])) AS [DB ID],
      [RecModel] AS [Recovery Model],
      ROUND ([LogSize], 2) AS [Log Size (MB)],
      ROUND ([LogUsed], 2) AS [Log Used (%)],
      [FileCount] AS [Log File Count],
      [VLFCount] AS [VLF Count]
     FROM msdb.dbo.SQLskillsAllInfo;

     -- Drop the temp tables
     --
     DROP TABLE msdb.dbo.SQLskillsLogSpace;
     DROP TABLE msdb.dbo.SQLskillsLogInfo;
     DROP TABLE msdb.dbo.SQLskillsAllInfo;
     DROP TABLE msdb.dbo.SQLskillsSysDb;
    END;
    GO

    -- Run the proc
    --
    EXEC sp_SQLskillsAllLogInfo;
    GO


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, February 24, 2011 11:46 AM
    Answerer
  • Hi Uri,

    Tnx for the excellent script, but i need to trigger E-mail automatically, if % of log goes above 80%.

     

    I have configured Database mail in my SQL servers.

     


    RM Thirunavukkarasu MCP, MCITP, ITIL V3 |Click "Propose As Answer" if solution is helpful to you|http://thiruna.blog.com. Thiru - MS SQL World
    Thursday, February 24, 2011 12:28 PM
  • Not a problem at all . Wrap this stored procedure into a job and schedule it .But within a stored procedure you need to check whether the output is great >80% and then  send email....

     

    Or  you can create an alert

     

    USE [msdb]
    GO
    EXEC msdb.dbo.sp_update_alert @name=N'DbData Log Size',
      @message_id=0,
      @severity=0,
      @enabled=1,
      @delay_between_responses=1800,
      @include_event_description_in=0,
      @database_name=N'',
      @notification_message=N'',
      @event_description_keyword=N'',
      @performance_condition=N'MSSQL$DBIN02:Databases|Percent Log Used|DbData|>|80',
      @wmi_namespace=N'',
      @wmi_query=N''
    GO
    EXEC msdb.dbo.sp_update_notification @alert_name=N'DbData Log Size', @operator_name=N'ProfileName', @notification_method = 1
    GO

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Naomi N Thursday, February 24, 2011 3:38 PM
    Thursday, February 24, 2011 12:45 PM
    Answerer
  • Not a problem at all . Wrap this stored procedure into a job and schedule it .But within a stored procedure you need to check whether the output is great >80% and then  send email....

     

    Or  you can create an alert

     

    USE [msdb]
    GO
    EXEC msdb.dbo.sp_update_alert @name=N'DbData Log Size',
      @message_id=0,
      @severity=0,
      @enabled=1,
      @delay_between_responses=1800,
      @include_event_description_in=0,
      @database_name=N'',
      @notification_message=N'',
      @event_description_keyword=N'',
      @performance_condition=N'MSSQL$DBIN02:Databases|Percent Log Used|DbData|>|80',
      @wmi_namespace=N'',
      @wmi_query=N''
    GO
    EXEC msdb.dbo.sp_update_notification @alert_name=N'DbData Log Size', @operator_name=N'ProfileName', @notification_method = 1
    GO

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, February 24, 2011 12:45 PM
    Answerer