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.
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
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 🙂
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
Thanks Uwe for your valuable comments… will work on implementing your suggestions.
Thanks,
Ahmad
Hi Aman .. for now I don’t have one… though you can extend this to a centrall solution.
Hi Ahmad,
Mind to share how can I use this script?
There is no need to create a new table for such case. You can directly use sys.dm_db_log_space_usage.