SQL Server Monitoring Transaction Log size with Email Alerts

Here’s a T-SQL to send automated email alerts.

whenever a SQL Server Monitoring Transaction Log size exceeds a specified threshold.

SET NOCOUNT ON
 
DECLARE @threshold int=5
-- step 1: Create temp table and record sqlperf data
CREATE TABLE #tloglist 
( 
databaseName sysname, 
logSize decimal(18,5), 
logUsed decimal(18,5), 
status INT
) 
 
INSERT INTO #tloglist 
       EXECUTE('DBCC SQLPERF(LOGSPACE)') 
 
-- step 2: get T-logs exceeding threshold size in html table format
DECLARE  @xml nvarchar(max)

SELECT @xml = Cast((SELECT databasename AS 'td',
'',
logsize AS 'td',
'',
logused AS 'td'
 
FROM #tloglist
WHERE logsize >= (@threshold*1024) 
FOR xml path('tr'), elements) AS NVARCHAR(max))
 
-- step 3: Specify table header and complete html formatting
Declare @body nvarchar(max)
SET @body =
'<html><body><H2>High T-Log Size </H2><table border = 1 BORDERCOLOR="Black"> <tr><th> Database </th> <th> LogSize </th> <th> LogUsed </th> </tr>'
SET @body = @body + @xml + '</table></body></html>'
 
-- step 4: send email if a T-log exceeds threshold
if(@xml is not null)
BEGIN
EXEC msdb.dbo.Sp_send_dbmail
@profile_name = 'Your database mail profile',
@body = @body,
@body_format ='html',
@recipients = 'your email id',
@subject = 'ALERT: High T-Log Size';
END
 
DROP TABLE #tloglist 
SET NOCOUNT OFF

The above T-SQL is very simple. The T-Log size across all database is inserted into a temp table by executing DBCC SQLPERF(LOGSPACE).

The temp table is then queried to get all databases with T-Log greater than the specified threshold (@threshold parameter).

The result set is wrapped into a html table format and is sent to specified address. The mail is sent using database mail stored procedures.

A snapshot of how email looks like is shown below.

1_SQL_Server_Monitoring_Transaction_Log_size_with_Email_Alerts

Schedule it as sql job per your convinient schedule to keep an eye on T-Log size.

 
Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

5 Comments on “SQL Server Monitoring Transaction Log size with Email Alerts”

  1. Hallo Ahmad,

    good approach but one of the solutions which will generate mass of emails (I know it from a global bank I’m working for!)) because no consideration of the following – IMPORTANT – settings:

    – what is the recovery model of the database?
    – what is the physical limit of the log file (is it unlimited)?
    – is the physical size of the log file at the limit?
    – does the underlying storage have enough free space for expanding the files?

    In consideration of these important information an alert should be generated but not in general! We get app. 200 mails with “treshold exceeded” and most of them have informational character because the log file is set to autogrowth with a dedicated limit which hasn’t been reached.

    Therefore a DBCC SQLPERF is not detailled enough for a deeper look into the environment of the system!

    But a good start is better than not to start 🙂

  2. Hi Ahmed,

    Very usefull script. Thanks

    We have around 5 instance on a cluster server. Is there any way to monitor log for production DB’s of all instances using Central management server.

    Thanks in advance.

    Regards
    Aman

Leave a Reply to Uwe Ricken Cancel reply

Your email address will not be published. Required fields are marked *