Need a script for log space monitoring

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.

Answer 1

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

Answer 2

/*============================================================================
 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/

Answer 3

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.

Answer 4

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/

Answer 5

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/