Tag Archives: SQL Agent Jobs

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 (172 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 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

 

SQL Server Administration – SQL Object Naming Convention

It’s useful to use a naming convention on Agent Jobs or Maintenance plans, this ensures they are grouped together and easier to find when you have lots of job or maintenance plans on your SQL Server.

Download (PDF Version) SQL-Server-Object-Naming-Conventions-Administration (876 downloads)

Download (Word Version) SQL-Server-Object-Naming-Conventions-Administration (1364 downloads)