By: Jeremy Kadlec | Last Updated: 2011-05-20 | Comments (4) | SQL Server Agent
I work in a fairly large and unwieldy SQL Server shop. Our environment is the wild west. I have DBAs and Developers on my team that create SQL Server Agent Jobs all of the time and there is never an operator setup to email anyone based on a failure condition. I know the right solution here is to "tame the wild west" by locking down the environment and setting up a change management process, but I think that is a topic for another day. Can you give me some idea of how to identify the new recently created SQL Server Agent Jobs and Jobs without an operator setup to email us for a failure condition? I would like to receive emails when either of these conditions occur. Check out the solution to this tip to learn how.
SolutionI am sorry to hear your environment is the "wild west" as you describe it. I have seen a few stations like that over the years. With the right team in place, taming the problem is possible, but we can leave that for another day as you requested.
In terms of identifying newly created SQL Server Agent Jobs and adding an operator, let's split those up and see about how to correct each of the items.
Identify newly created SQL Server Agent Jobs
The first portion of this process is identifying newly created SQL Server Agent Jobs. In the code below we are querying the dbo.sysjobs table in the MSDB database. The WHERE clause is just returning records where the date_create value is from the last fifteen days.
SELECT [Name], [Date_Created] FROM MSDB.dbo.sysjobs WHERE [Date_Created] BETWEEN DATEADD(dd, -15, GETDATE()) AND GETDATE(); GO
This is pretty simple code and should do the trick on the first portion of the process. This code can be run as a portion of another job and emailed to you so you know a new job is created. With this information we can move on to the second step where we identify the jobs without an operator setup to notify based on a failure condition.
Identify SQL Server Agent Jobs without any notification setup
SQL Server Agent Jobs can be setup for the following types of notifications:
- When the job succeeds
- When the job fails
- When the job completes
Based on these conditions, it is possible to email, page or net send an operator. That could be the same operator for each condition or different operators for each condition. Since you specifically requested email based alerts, we will check for that condition.
In the code below we are once again querying the dbo.sysjobs table in the MSDB database. This query once again is very simple, but in this circumstance our WHERE clause is a little different. The first condition is where the notify_level_email column equals 0, which means when no notification is setup. The second condition is where the enabled column equals one, which means the job is enabled.
SELECT [Name], [Date_Created], [Enabled] FROM MSDB.dbo.sysjobs WHERE [Notify_Level_Email] = 0 AND [Enabled] = 1; GO
Since we have a simple SELECT statement to identify the jobs without an email based operator for a failure condition and since you wanted to update those jobs, let's look at two more queries to update the jobs without the notification you requested.
In the first query we are capturing operator information to complete the UPDATE logic listed second. In this query we are querying the dbo.sysoperators table in the MSDB database. The query is retrieving enabled operators in ascending order.
SELECT [ID], [Name], [Enabled] FROM MSDB.dbo.sysoperators WHERE [Enabled] = 1 ORDER BY [Name]; GO
If this query does not return any data either no operators have been setup or the operators are disabled. Check out this tip to setup operators.
In the UPDATE statement we are using the ID from the first query to update the dbo.sysjobs table of the MSDB database. In this circumstance we are updating all jobs where the notification_level_email column is not setup for any notification and the job is enabled.
UPDATE S SET S.[notify_level_email] = 2, S.[notify_email_operator_id] = -- <ID from the previous query> FROM MSDB.dbo.sysjobs S WHERE S.[Notify_Level_Email] = 0 AND S.[Enabled] = 1; GO
These queries should fulfill your request, but there are some caveats. It could be possible that an email based operator is not setup, but a pager or net send operator is setup. So keep this in mind. In addition, the jobs could also be setup to notify operators on success or when a job completes, which could be a failure or a success condition. That is probably not the case in your environment because SQL Sever Jobs are not setup with notifications by default, but other environments may need to modify the queries slightly based on their environment.
- I feel for you, as I am sure many other DBAs do, having to try to tame the "wild west". I will see if I can come up with a tip for you to try to address the issues.
- In terms of resolving your immediate issue, hopefully the queries in this tip will offer you some insight into SQL Server Agent Jobs getting created. Hopefully you can implement these queries as a portion of your monitoring process.
- To take things a step further, I would recommend setting up comments in each of your jobs each time you make a change in order to retain some history on a per job basis. The comments could be as simple as "date | DBA | comments", but I will leave that up for you to decide.
- As a final recommendation, read Ken Simmon's tip on Enabling a Fail-Safe Operator in SQL Server Agent in case there is an issue with MSDB or if an operator is not setup to receive alerts during a period of time when an issue occurs.
- Check out the SQL Server Agent tips to learn new ways to manage this key portion of SQL Server.
Last Updated: 2011-05-20
About the author
View all my tips