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 = 'email@example.com' ,@body = 'Backup job is still running on instance' ,@subject = 'Backup job is still running on instace' ,@importance = 'high'; END