SQL Server Agent Jobs without an Operator

By: Jeremy Kadlec | Comments (4) | Related: 1 | 2 | > SQL Server Agent

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.

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.

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

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Saturday, May 21, 2011 - 6:56:52 AM - Jeremy Kadlec

Joel,

Thank you for sharing your code and the explanations.

Thank you,
Jeremy Kadlec

Friday, May 20, 2011 - 5:08:18 PM - Joel Gores

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

I have 4 steps below to create the solution:

1. create base table

2. seed the tables

3. working section of code (written in SSIS executed by a sql job)

4. code to keep it current (written as t-sql in a job)

----******************************************************************************************

1. Script for table objects

EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JobHistoryAssignmentGroup]') AND type in (N'U'

))

DROP

TABLE [dbo].

[JobHistoryAssignmentGroup]

EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JobHistoryLastNotify]') AND type in (N'U'

))

DROP

TABLE [dbo].

[JobHistoryLastNotify]

EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JobHistoryNotictionToSend]') AND type in (N'U'

))

DROP

TABLE [dbo].

[JobHistoryNotictionToSend]

SET

ANSI_NULLS

SET

QUOTED_IDENTIFIER

SET

ANSI_PADDING

CREATE

TABLE [dbo].[JobHistoryNotictionToSend]

(

[JobHistoryNotictionToSend] [int]

IDENTITY(1,1) NOT

NULL,

[name] [varchar]

(128)

NULL,

[Server] [varchar]

(100)

NULL,

[StartTime] [datetime]

NULL,

[JobHistoryAssignmentGroupId] [int]

NULL,

[Processed] [bit]

NULL,

[ProcessedDate] [datetime]

NULL,

CONSTRAINT [PK_JobHistoryNotictionToSend] PRIMARY KEY CLUSTERED

(

[JobHistoryNotictionToSend]

ASC

)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON

[PRIMARY]

)

[PRIMARY]

SET

ANSI_PADDING

OFF

SET

ANSI_NULLS

SET

QUOTED_IDENTIFIER

NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JobHistoryLastNotify]') AND type in (N'U'

))

BEGIN

CREATE

TABLE [dbo].[JobHistoryLastNotify]

(

[JobHistoryLastNotifyId] [int]

IDENTITY(1,1) NOT

NULL,

[Start_Instance_Id] [int]

NULL,

[End_Instance_Id] [int]

NULL,

CONSTRAINT [PK_JobHistoryLastNotify] PRIMARY KEY CLUSTERED

(

[JobHistoryLastNotifyId]

ASC

)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON

[PRIMARY]

)

[PRIMARY]

END

SET

ANSI_NULLS

SET

QUOTED_IDENTIFIER

SET

ANSI_PADDING

NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JobHistoryAssignmentGroup]') AND type in (N'U'

))

BEGIN

CREATE

TABLE [dbo].[JobHistoryAssignmentGroup]

(

[JobHistoryAssignmentGroupId] [int]

IDENTITY(1,1) NOT

NULL,

[JobName] [varchar]

(128)

NULL,

[Location] [varchar]

(100)

NULL,

[AssignmentGroup] [varchar]

(100)

NULL,

[Impact] [int]

NULL,

[Urgency] [int]

NULL,

[Category] [varchar]

(100)

NULL,

[SubCategory] [varchar]

(100)

NULL,

[BusinessUnit] [varchar]

(100)

NULL,

[ShortDescription] [varchar]

(500)

NULL,

[Other] [varchar]

(500)

NULL,

CONSTRAINT [PK_JobHistoryAssignmentGroup] PRIMARY KEY CLUSTERED

(

[JobHistoryAssignmentGroupId]

ASC

)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON

[PRIMARY]

)

[PRIMARY]

END

SET

ANSI_PADDING

OFF

----******************************************************************************************

2. Scripts to seed the data, this is important so the first time the job runs it doesn’t send a job notification for prior failures.

insert

into

JobHistoryAssignmentGroup

VALUES

('UNKNOWN','123 ANYWHERE','Database SQL',2,1,'Software - IT','SQL Server','Operations','',''

)

insert

into

JobHistoryAssignmentGroup

select

name, '123 ANYWHERE','Database SQL',2,1,'Software - IT','SQL Server','Operations','',

from

msdb.dbo.

sysjobs

insert

into

JobHistoryLastNotify

select

max(instance_id)-1, max(instance_id

)

from

msdb.dbo.

sysjobhistory

----******************************************************************************************

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 "?"

----******************************************************************************************

3A)

--Check to ensure that the last run did not leave any unprocessed records
If 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

    Delete From dbo.JobHistoryLastNotify
    Where JobHistoryLastNotifyId not in
      (
      Select MAX(JobHistoryLastNotifyId) From dbo.JobHistoryLastNotify
      )
    --get jobs to send with some properties
    Declare @start int, @end int
    Select @start = Start_instance_id ,
      @end = End_instance_id
    From dbo.JobHistoryLastNotify

    Insert into dbo.JobHistoryNotictionToSend
    (name ,Server ,StartTime, Processed )
    Select sj.name, server,
      cast(substring(replace(str(Run_Date,8),' ','0'),5,2) + '/' + substring(replace(str(Run_Date,8),' ','0'),7,2) + '/' + substring(replace(str(Run_Date,8),' ','0'),1,4) + ' '+
      substring(replace(str(Run_Time,6),' ','0'),1,2) + ':' + substring(replace(str(Run_Time,6),' ','0'),3,2) + ':' + substring(replace(str(Run_Time,6),' ','0'),5,2)as datetime) as starttime
      ,0--processed
    From msdb.dbo.sysjobhistory sjh inner join
      msdb.dbo.sysjobs sj on sjh.job_id = sj.job_id
    Where instance_id >= @start
    and  instance_id <= @end
    and  sjh.step_id = 0
    and  upper(message) not like('%CUSTOM FILTER%')
    AND  sjh.run_status not in(1,4)


   End
End

----******************************************************************************************

3B)

declare @Recipient varchar(50)
set @Recipient = ?

--Check to see if we have assignment
If 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

declare @counter int

Declare @sql varchar(8000)
set @sql = ''
set @counter = (select min(JobHistoryNotictionToSend) from JobHistoryNotictionToSend where processed = 0)

while @counter <= (select MAX(JobHistoryNotictionToSend) from JobHistoryNotictionToSend)
Begin

select @sql =

'exec msdb.dbo.sp_send_dbmail
@profile_name = ''secretname'',
@recipients = '''+@Recipient+''',
    @subject = ''SQL Job Auto Assignment Information'',
    @body =
    ''Incident Information:
Location:'+Location+'
Assignment group:'+AssignmentGroup+'
Impact:'+convert(varchar(10),Impact)+'
Urgency:'+convert(varchar(10),Urgency)+'
Category:'+Category+'
Subcategory:'+SubCategory+'
Business Unit:'+BusinessUnit+'
Short description:'+nts.name +' (' +nts.Server +') '+ replace(convert(varchar(100),nts.StartTime,0),':','.') +'
JobHistoryAssignmentGroupId: '+convert(varchar(10),jhag.JobHistoryAssignmentGroupId)+'
'' '
from dbo.JobHistoryNotictionToSend nts inner join
   dbo.JobHistoryAssignmentGroup jhag on nts.JobHistoryAssignmentGroupId = jhag.JobHistoryAssignmentGroupId
where @counter = JobHistoryNotictionToSend
exec (@sql)

update JobHistoryNotictionToSend
set  processed = 1,
   ProcessedDate = Getdate()
where JobHistoryNotictionToSend = @counter
--print @sql

set @counter = @counter +1
continue
end

--keep 45 days of history
delete
from dbo.JobHistoryNotictionToSend
where ProcessedDate < dateadd(d,-45,getdate())

----******************************************************************************************

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 JobHistoryAssignmentGroup
where jobname <> 'UNKNOWN' and jobname not in(select name from msdb.dbo.sysjobs)

insert into JobHistoryAssignmentGroup
select name, '123 ANYWHERE','Database SQL',2,1,'Software - IT','SQL Server','Operations','',''
from msdb.dbo.sysjobs
where name not in(select jobname from JobHistoryAssignmentGroup)

----******************************************************************************************

That’s it, Ongoing job notifications on any server for any job.

Friday, May 20, 2011 - 12:00:32 PM - Jeremy Kadlec

Joel,

Thank you for sharing your solution to this problem. Feel free to post your code in this forum to help members of the community.

Thank you,
Jeremy Kadlec

Friday, May 20, 2011 - 8:21:49 AM - Joel Gores

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.