SQL Server Agent Jobs without an Operator

Problem

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.

Solution

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

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.

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.

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.

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.

Next Steps

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.

I'll give it a shot to post my code. I happen to send the failure notifications to a single recipient which is an interface to our ticketing system. You can easily set up another column in the JobHistoryAssignmentGroup table to send email to different people if you desire. I am not going to post information about mail set up which is required for this, but you can find documentation for that with Microsoft. http://msdn.microsoft.com/en-us/library/ms175887(v=SQL.100).aspx

I currently have this running in production on SQL 2000, 2005, 2008. There is some slight modifications for SQL 2000 (you know you have one of these out there too). Since we cannot use SSIS I have it as a simple t-sql step in a job and I use xp_smtp_sendmail for email from http://sqldev.net/downloads/xpsmtp/default.html

3. Code that is in the SQL tasks inside of an SSIS Package (I cannot share the whole package at this time, but this is 99% of the working solution) There is a script task before this that determines if I am on a production server so I don’t send emails on development machines to the same recipients. you will see this as an input parameter later on 3b by use of the "?"

--Check to ensure that the last run did not leave any unprocessed recordsIf not exists (select top 1 processed from JobHistoryNotictionToSend where processed = 0) Begin Declare @MaxInstance_Id int Declare @MinInstance_Id int

Select @MinInstance_Id = MIN(instance_id), @MaxInstance_Id = MAX(instance_id) From msdb.dbo.sysjobhistory sjh Where sjh.instance_id > (select MAX(End_instance_id) from JobHistoryLastNotify) --check to see that we have new records, if this doesnt happen a null value is updated and the next run selects everything from sysjobhistory If @MaxInstance_Id is not null Begin --we have new records to evaluate so we can insert and drop the last run Insert Into JobHistoryLastNotify Select @MinInstance_Id, @MaxInstance_Id

--Check to see if we have assignmentIf exists (select top 1 JobHistoryAssignmentGroupId from JobHistoryNotictionToSend where JobHistoryAssignmentGroupId is null) Begin update JobHistoryNotictionToSend set JobHistoryNotictionToSend.JobHistoryAssignmentGroupId = jhag.JobHistoryAssignmentGroupId from dbo.JobHistoryNotictionToSend jhnts inner join dbo.JobHistoryAssignmentGroup jhag on jhnts.name = jhag.jobname

update JobHistoryNotictionToSend set JobHistoryNotictionToSend.JobHistoryAssignmentGroupId = jhag.JobHistoryAssignmentGroupId from dbo.JobHistoryAssignmentGroup jhag where jhag.jobname ='UNKNOWN' and JobHistoryNotictionToSend.JobHistoryAssignmentGroupId IS NULL End

4) Code to run in another job to keep the new/delete jobs in order. This can be ran as often as you would like, daily is probably sufficient, but it depends on how often jobs are added/dropped. We have scheduled deployments so i run this weekly. if you know of a big push you can manually kick it off. It also adds the new jobs to default to our group, so its helpful if we know about the job first so we can add the properties for proper support.

delete JobHistoryAssignmentGroupwhere jobname <> 'UNKNOWN' and jobname not in(select name from msdb.dbo.sysjobs)

I had a similar situation, but I took a slightly different approach. Configuring SQL 2005 and 2008 DB Mail created the method to send notifications. I then set up a few jobs and tables. The process is fairly simple, by looking at sysjobhistory I know of any job failure. (This handles all new failures but not past ones.) From here I use one of the custom table which hold the properties of the job name. Those properties can be anything you choose, but it should hold the value of recipient. A job runs every 10 minutes and sends emails based on failures and the other job looks at sysjobs for new or deleted items and updates the job property table. And there is it, an automated failure notification method. There is some minor maintenance to the process which is keeping the recipient list updated. But for any job that does not have an owner, it defaults to our DBA group. Putting it back in our own hands rarely means that items are not getting work and assigned properly. The other aspect to watch out for is the size of sysjobhistory, you dont want to send emails or spend resources evaluating jobs that have already been addressed. I keep a history record of the last run of Instance_Id from sysjobhistory so the process knows where to start off. This solution is packaged into a few deployment scripts and an SSIS package and it now part of my stand sql build.