Paging Dr. DBA

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;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s