Alert for long-running SQL database backups

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.

1
2
3
4
5
6
7
8
9
10
11
12
13

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