Alert for long-running SQL datbase backups

23. August 2010 by Bennett.

One of my daily tasks is to do a quick check of each SQL Server instance using Activity Monitor, sp_who2, or a DMV-based script.  Sometimes I get busy and forget to do this task.  Today I broke down and wrote a simple script that is executed by a SQL Agent job.  It runs at 8:00 AM, and just goes out to all the instances and checks to see if any backups are still running — if any are still running, an e-mail alert is raised.

The essence of the script follows.  You would might want to modify it to iterate through a list of instances.

IF EXISTS
(SELECT * FROM instance.MASTER.sys.sysprocesses
WHERE cmd = ‘backup database’
AND program_name = ‘SQL Management’)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Master’,
@recipients = ‘mailbox@domain.com’,
@body = ‘Backup job is still running on instance’,
@subject = ‘Backup job is still running on instace’,
@importance =  ‘high’;
END


Posted in SQL Server | Print | No Comments »