Click here to monitor SSC

Jonathan has been working with SQL Server since 1999. He enjoys performance tuning, development and using SQL Server to provide appropriate business solutions. He is the founder and leader of the PASS SQL South West user group http://www.sqlsouthwest.co.uk , is a moderator at SQL Q + A forum ask.sqlservercentral.com and is on twitter at @fatherjack. He has spoken at SQLBits and SQL in the City, SQL Saturdays and local user groups across the UK and Europe.

Jobs – are your SQL Agent jobs talking to you enough?

Published 20 October 2011 3:00 am

Most DBAs will have at least a couple of servers that have SQL Agent jobs that are scheduled to do various things on a regular basis. There is a whole host of supporting configuration settings for these jobs but some of the most important are notifications.

Notification settings are there to keep you up to date on how your job executions went. You have options on types of notification – email, pager, net send, or an entry in the SQL Server Event Log and you get options on when each of these channels gets a message – on completion, on successful completion, on failure, or no action. If you know all about the creation of operators and notifications then feel free to skip down to the last section where there are a couple of sections of TSQL that will help make sure you keep everything configured right.

SQLAgentNotifications06The notifications are really easy to configure and are well worth the few clicks it takes. first things first, you need an Operator or two set up. These are objects in SQL Server that represent people or better yet, teams of people, on your network that have an interest in the job status. It is best to set up operators that represent a team, with a group email account so that regardless of staff changes and occasional absences the notification messages will reach someone, every time.

SQLAgentNotifications04As you can see on my server there are 3 operators, two sadly appear to be individuals but at least one refers to a team.

SQLAgentNotifications01Now, back to the jobs. On this job we are configuring the notifications to go to the DBA_Team operator. We have ticked we want an email to be sent on the condition that the job fails and we want an entry in the SQL Event Log whenever the job completes.

This means anyone in the DBAteam email group will get told if the job fails to complete successfully and every time the job completes (either successfully or not) the Event Log will be updated.

Now knowing this is all well and good, and from now on all your jobs can be set up with this sort of scheme in place but what about if you take over a server that hasn’t been configured quite right previously or you have been learning on the job since the last DBA left. There may be hundreds of SQL Agent job with dozens of operators and it would be a life’s work to check every one via the SSMS interface. How about a script then?


USE [msdb]
GO

-- list all jobs in order of notification settings.
-- jobs at the top of the list may need more rapid attention than those lower down
SELECT  [j].[name],
       
CASEWHEN[j].[notify_level_eventlog]=1THEN'On success'
           
WHEN[j].[notify_level_eventlog]=2THEN'On failure'
           
WHEN[j].[notify_level_eventlog]=3THEN'On completion'
           
ELSE'| = - No alert - = |'
       
END AS[Event Log Entry],
       
CASEWHEN[j].[notify_level_page]=1THEN'On success'
           
WHEN[j].[notify_level_page]=2THEN'On failure'
           
WHEN[j].[notify_level_page]=3THEN'On completion'
           
ELSE'| = - No alert - = |'
       
END AS[Page Alert],
       
CASEWHEN[j].[notify_level_netsend]=1THEN'On success'
           
WHEN[j].[notify_level_netsend]=2THEN'On failure'
           
WHEN[j].[notify_level_netsend]=3THEN'On completion'
           
ELSE'| = - No alert - = |'
       
END AS[Net Send Alert],
       
CASEWHEN[j].[notify_level_email]=1THEN'On success'
           
WHEN[j].[notify_level_email]=2THEN'On failure'
           
WHEN[j].[notify_level_email]=3THEN'On completion'
           
ELSE'| = - No alert - = |'
       
END AS[Email Alert]
FROM    [dbo].[sysjobs]ASj
ORDER BY([j].[notify_level_email]+[j].[notify_level_eventlog]
         
+[j].[notify_level_page]+[j].[notify_level_netsend])DESC

This will give results like this, where you can easily see that some jobs are sending notifications under certain conditions whereas other are not. Those are the ones you need to review and alter.SQLAgentNotificationsResults01

If you want to check up on the operators on your server then this is another useful piece of code:

--	list all operators that are either not enabled or are not set to 
-- receive any notifications
SELECT  [o].[name] ,
       
CASE WHEN [o].[enabled] = 0 THEN '| = - Not Enabled - = |'
            
ELSE 'Enabled'
       
END AS [Enabled] ,
       
CASE WHEN [o].[email_address] IS NULL THEN '| = - No details - = |'
            
ELSE 'Enabled'
       
END AS [Email Details] ,
       
CASE WHEN [o].[pager_address] IS NULL THEN '| = - No details - = |'
            
ELSE 'Enabled'
       
END AS [Pager details] ,
       
CASE WHEN [o].[netsend_address] IS NULL THEN '| = - No details - = |'
            
ELSE 'Enabled'
       
END AS [NetSend Details]
FROM    [dbo].[sysoperators] AS o
WHERE   [o].[enabled] = 0
       
OR ( [o].[email_address] IS NULL
             OR
[o].[pager_address] IS NULL
             OR
[o].[netsend_address] IS NULL
           )
ORDER BY [o].[enabled]

The results of this script show which operators are cued up for what sort of alert, if any at all. Ted appears to be getting it easy currently.

19-10-2011 20-14-24

As always, understand any script you get from the internet before you execute it on your own servers.

If you haven’t already heard, Red Gate are running some big events currently – there is DBA in Space where a lucky prize winner will win the chance to take a flight to the edge of space, all the details on how to enter are at www.dbainspace.com. On a more accessible level they also have the SQL in the City event in Los Angeles on 28th October, all the details (along with a video from the first event in London earlier this year) are at www.sqlinthecity.com.

6 Responses to “Jobs – are your SQL Agent jobs talking to you enough?”

  1. lexorreymond15 says:

    nice post!

Leave a Reply