Finding SQL Server Job details having no operator for notification

Posted: May 7, 2013 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , , , ,

As a DBA, sometimes it’s may be happened you forgot to set a notification to a job, here is a very simple script to find out which job have notification or not.

Select SJ.Name Job_Name,
Case SJ.Enabled when 1 then ‘Enabled’ else ‘Disabled’ end Enable_Status,
SJ.description Job_Description,
SUSER_SNAME(owner_sid) Job_Owner,
SJ.date_created 
Job_Created_Date,
SJ.date_modified 
Job_Modified_Date,
SP.Name 
Operator_Name,
SP.email_address 
Emails,
SP.last_email_date 
Last_Email_Date,
SP.last_email_time 
Last_Email_Time
from 
msdb..sysjobs SJ
LEFT 
JOIN msdb..sysoperators SP on SP.ID = SJ.notify_email_operator_id

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