20 October 2011

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

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.

SQLAgentNotifications06_thumb.pngThe 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.

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

SQLAgentNotifications01_thumb.pngNow, 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?

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_thumb.png

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

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_thumb.png

FriendsOfRedGate_2011_OnWhite_RGB_300px.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.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 2232 times – thanks for reading.

  • Rate
    [Total: 0    Average: 0/5]
  • Share

Jonathan Allen

View all articles by Jonathan Allen