Advertisements

SQL-BOX.COM

–SQL Server Storage

Log Space Alert- You decide when you want get a Alert

LOG FILE GROWTH – FREE SPACE ALERT

BY Pavan Srirangam, 2011/02/02

This post is especially for all DBAs and that too who have faced the problem of log file growing frequently.

I’m not going to describe here why it grows? If you want to dig and find the reasons there are plenty of resources to help you out.

I just want share about how we can avoid or be aware of a situation where our production database is no more available to users because of log file is full.

First what we are going to do is creating an alert to send out an e-mail saying that your log file is going to filled up Isn’t it cool ? And What if you can have chance specifying at what free space you wish to get alert? Exactly, this is what we are going do now.

I assume DB-mail is configured to your e-mail in your server. How to configure DB-mail? See here.

I also assume you know how create and schedule a job in SQL server Agent.

Our goal will be accomplished in only two steps,

  1. Creating a user defined stored procedure in your user database then,
  2. Creating a job with a Schedule to run that stored procedure how frequently you want to check the log space (you-decide).

Below is the script to create stored procedure that sends out an e-mail alert when log fills out to certain value (it depends on your requirement). In my case if my log file is of total 60GB then I set the benchmark to 50 GB so that I can have some time to do some tactics to figure it out what transactions are making it to fill or some other reasons and take some precautions to avoid it. Benchmark is nothing but when you want to get an alert once log reaches to that value or size.

Here is the script that helped me; hopefully it helps you I guess.

/*This procedure sends out an alert message when LOG SPACE is below a predefined value (benchmark).This procedure can be scheduled to run daily as per your requirement so that DBA can act quickly to address this issue.
*/
CREATE PROC [dbo].[usp_logspacealert]
– Set value in MB at what free space you wants get alert
@logspacebenchmark int = ’10240′–you can specify your own value
AS
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N’[tempdb]..[#files_size]‘))
DROP TABLE #files_size
DECLARE @logfreespace INT
DECLARE @AlertMessage VARCHAR(500)
DECLARE @MailSubject VARCHAR(100)
/* Populate #files_size with actual size of the file */
SELECT
a.file_id,
LOGICAL_NAME = a.name,
PHYSICAL_FILENAME = a.physical_name,
FILEGROUP_NAME = b.name,
FILE_SIZE_MB = CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)),
SPACE_USED_MB = CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,’SpaceUsed’)/128.000,2)),
FREE_SPACE_MB = CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,’SpaceUsed’))/128.000,2))
INTO #files_size
FROM  sys.database_files a
LEFT OUTER JOIN sys.data_spaces b
ON a.data_space_id = b.data_space_id
 
/*selecting only log free space as we require only that here*/
SELECT @logfreespace = FREE_SPACE_MB
FROM
#files_size
where LOGICAL_NAME = ‘your log file name’
IF @logfreespace <= @logspacebenchmark
Begin
SET @MailSubject = ‘log free space is low on for database name on’ + @@SERVERNAME
SET @AlertMessage = ‘LOG file name on ‘ + @@SERVERNAME + ‘ has only ‘ +  CAST(@logfreespace AS VARCHAR) + ‘ MB left. Please free up space on this drive.please check the log backup history may be job failed try to take manual backup of log or contact administrator. ‘
– Send out email
EXEC msdb.dbo.sp_send_dbmail —This for you
@profile_name =  ‘firstname lastname’
, @recipients  =  ‘username@mail.net’
, @subject     =  @MailSubject
, @body        =  @AlertMessage
, @body_format = ‘TEXT’
EXEC msdb.dbo.sp_send_dbmail –This is for your manager or other responsible person
@profile_name =  ‘firstname lastname’
, @recipients  =  ‘username@mail.net’
, @subject     =  @MailSubject
, @body        =  @AlertMessage
, @body_format = ‘TEXT’
End  DROP TABLE #files_size

As this is my first article ever published, your comments and suggestions will give me lots of strength to come up with some other most useful SQL stuff. Thank you all very much for reading my post.

Advertisements

2 responses to “Log Space Alert- You decide when you want get a Alert

  1. Rahul June 10, 2011 at 7:08 am

    Pavan,
    Firstly I congratulate you for launching SQLFrendz site and for your first article, I wish you all success.

    I liked your article which gives DBA’ s get to know what the problem is with no effort, and I would like to know what happens
    1. When job fails or
    2. When job is successful or failure and further fails to notify via e-mail.

    Good Luck,
    Rahul.
    first articale and the

    • sqlfrndz June 10, 2011 at 9:09 am

      Even in the time job failure, you can query the #files_size table for the data. If this is the case then make this #files_size temp table to actual table in your database.

      As long as your db mail is configured to your profile account it should not fail, even in that the messages will be qued in service broker.

      Query MSDB for qued messages execute the following
      sysmail_help_queue_sp @queue_type = ‘Mail’ ;,

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: