We have an on-call rotation at work. One of the things we watch is SQL Agent jobs failing. Some of these can fail gracefully—they just restart and pick up where they left off—but some of them tell us that we need to act now. In order to do this, we make use of the SQL Agent pager functionality, sending text messages to the on-call DBA. This works, but every once in a while, you find out that there were some jobs which weren’t set up to notify the on-call phone upon failure. The following script lets you check which jobs are attached to a pager and which are not.
USE [msdb] GO SELECT sj.job_id, sj.name as JobName, sj.[enabled], sj.notify_level_page, sj.notify_page_operator_id, so.name as PagerOperator FROM dbo.sysjobs sj LEFT OUTER JOIN dbo.sysoperators so ON sj.notify_page_operator_id = so.id WHERE sj.name like '%RelevantJobs%' ORDER BY JobName;