I'm about to have a couple of weeks holiday over Christmas (lucky me!) and one of the tasks my boss asked me to do before I went was make sure that e-mail notifications that were directed to me only, should be re-assigned to our entire team.
We rely on SQL Server Agent Jobs and these can be set to notify operators when the job fails, succeeds, or completes (regardless of the outcome). After quickly looking through a couple of jobs, I found plenty that were only notifying me (oops). I needed a more foolproof way to identify the jobs that needed changing.
In addition to setting the e-mail operator to be our team e-mail, I also took the opportunity to also make sure that the job writes to the Event Log on failure and has a valid category (I blogged about Job Categories - and how you can set up your own - back in April 2008).
Here's the query I used, which lists all jobs apart from Reporting Services subscription jobs (which have GUIDs as names) and disabled jobs:
-
--*** SQL Server Agent Jobs and selected properties, by Thomas Williams ***
-
--*** this query has been tested on SQL Server 2005 only ***
-
--For more information on the sysjobs table, see:
-
-- http://msdn.microsoft.com/en-us/library/ms189817.aspx
-
--For more information on sysoperators, see:
-
-- http://msdn.microsoft.com/en-us/library/ms188406.aspx
-
SELECT --Job name
-
[Job Name] = sj.[name],
-
--Job owner
-
[Owner] = SUSER_SNAME(sj.[owner_sid]),
-
--Job category e.g. "Database Maintenance", "Report Server", "[Uncategorized (Local)]"
-
[Category] = c.[name],
-
--Operator to be e-mailed (may be NULL)
-
[Email Operator] = o.[name],
-
--notification in Event log (notify when job fails, succeeds, always, never)
-
[Event LOG Notification] = CASE sj.[notify_level_eventlog]
-
WHEN 0 THEN 'Never'
-
WHEN 1 THEN 'When the job succeeds'
-
WHEN 2 THEN 'When the job fails'
-
WHEN 3 THEN 'When the job completes (regardless of the job outcome)'
-
ELSE 'UNKNOWN' END,
-
--e-mail notification (notify when job fails, succeeds, always, never)
-
[Email Notification] = CASE sj.[notify_level_email]
-
WHEN 0 THEN 'Never'
-
WHEN 1 THEN 'When the job succeeds'
-
WHEN 2 THEN 'When the job fails'
-
WHEN 3 THEN 'When the job completes (regardless of the job outcome)'
-
ELSE 'UNKNOWN' END
-
FROM msdb.dbo.[sysjobs] sj LEFT OUTER JOIN
-
--E-Mail Operator
-
msdb.dbo.[sysoperators] o ON
-
sj.[notify_email_operator_id] = o.[id] LEFT OUTER JOIN
-
--Job categories
-
msdb.dbo.[syscategories] C ON
-
sj.[category_id] = c.[category_id]
-
WHERE --ignore auto-created jobs (Reporting Services schedules)
-
NOT (sj.[name] LIKE '_____________-____-____-____________') AND
-
--only enabled Jobs
-
sj.[enabled] = 1
-
ORDER BY sj.[name]
One thing I came across while auditing jobs was e-mail notifications on success or completion (rather than just on failure). Our team agreed that if we needed a special notification on success or completion that we would use sp_send_dbmail in a job step, and leave the job notifications for errors only.
Tags: sql server, sql agent, job, sysjobs
posted @ Tuesday, December 02, 2008 1:45 PM