Tag Archives: SQL Agent

Finding which Operators are setup against SQL Agent Job Notifications

As a minimum every SQL Agent Job should have an operator (or DBA) notification setup ideally for failure or completion.

Agent Job Properties

How do I check which operators are setup against my Agent Jobs I hear you say!

Well run this query below to find out.  If you have any agent jobs return as ‘No email notification configured’ in the [Notify_level_email] field then an email notification is not setup and you should look into these as a priority (unless you have other methods of monitoring Agent Jobs that is!)

SELECT SJ.NAME, SO.[name],SO.[email_address],
[notify_level_email] =
CASE notify_level_email
     WHEN '1' THEN 'When the job succeeds'
     WHEN '2' THEN 'When the job fails'
     WHEN '3' THEN 'When the job completes'
     ELSE 'No email notification configured'
END
FROM
msdb.dbo.sysjobs SJ LEFT JOIN [msdb].[dbo].[sysoperators] SO 
ON SJ.notify_email_operator_id = SO.ID
ORDER BY SO.ID, SJ.NAME

Download  Finding-which-Operators-are-setup-against-SQL-Agent-Job-Notifications (159 downloads)

The example below shows the operators setup against Agent Jobs and the email address the notification will be sent to, the first row shows that no operator is setup on this job for email notifications, if this job fails no-one may be aware.

Agent Job Notifications

 

There’s a few pre-requisites that your need to ensure are setup first for Agent Email Notifications to work:
1.  Database Mail should be configured and working (see this great article on SQL Server Central if you don’t know how to setup Database Mail)
2.  Agent Operators should be setup with valid email addresses (see my post on recommended operators to setup)  
3.  Agent Jobs should have an email notification configured ideally for ‘When jobs the fails’

SQL Agent Operators Recommended Setup

Database Mail should be configured on your SQL Server, if it isn’t get it done now there are no excuses, this is one of the main methods that can be used for finding out when there are issues with your SQL Server!

It’s a good idea to setup an operator with a distribution email group email address so if a DBA leaves or moves onto another position the distribution group can be easily updated once in Active Directory as opposed to being updated on every SQL Server.

DBA Team Operator

 

You could also create additional distribution email groups and add them as operators this is useful if other administrators needs to be aware of certain Agent Jobs failing such as say BI Jobs.

DBA Team BI Operator

SQL Agent Jobs refresh times when using sysjobschedules table

A recent issue I was looking at confused me as we were adding SQL Agent Jobs to run a SSRS report but the agent job was not appearing in the ‘sysjobschedules’ table, eventually the job did appear but why did it not appear straight away.

The reason is the the sysjobschedules table refreshes every 20 minutes, which may affect the values returned by the sp_help_jobschedule stored procedure.

Read the article here on MSDN