Notifications

I have a script to check that I have got my SQL Server Agent jobs right.

#1 Owner not SA

The default owner of the agent job you set up is you.  Well, that's fine, until you leave the company, they disable your account, and everything falls over.  So I tend to make sure that all the jobs have the owner SA.  In a prefect world you might think about setting up an account just to run SQL Agent jobs.  I'll make an exception for SharePoint jobs, which I prefer not to touch with a BargePole



-- 1 - Owner not SA
--**************************************************************
select
 '1 - Owner not SA' as Problem,
       suser_sname(sj.owner_sid ) as Owner,
       replace(replace(sj.name,char(10),' - '),char(13),' ') as Job_Name,
       replace(replace(sj.[Description],char(10),' - '),char(13),' ') as Job_Description
from
       msdb.[dbo].[sysjobs]                          sj
where
 suser_sname(sj.owner_sid ) not in
 (
  'SA',
  'SharePoint'
 )
order by
       [enabled] desc,
       sj.name



#2 Jobs with no description

Whoever set the job up didn't bother to put in a description.  Well, you don't actually NEED a description.  But when the job fails, you will be left sitting there wondering what the heck the job is supposed to be doing and who set it up and when

So just a brief sentence, maybe a paragraph, plus the name of the guy to contact and the date.  Even if it's you - chances are that you won't remember it in a year's time - that description will help a lot


-- 2 - No description available.
--**************************************************************
select
 '2 - No description available' as Problem,
       suser_sname(sj.owner_sid ) as Owner,
       replace(replace(sj.name,char(10),' - '),char(13),' ') as Job_Name,
       replace(replace(sj.[Description],char(10),' - '),char(13),' ') as Job_Description
from
       msdb.[dbo].[sysjobs]                          sj
where
 sj.[Description] = 'No description available.'
or
 sj.[Description] = ''
order by
       [enabled] desc,
       sj.name



#3 Not Logging completion

You don't NEED to put stuff in the logs
But if you don't, what's the point of having a log?


-- 3 - Jobs which are not set to update event log on completion
--**************************************************************
select
 '3 - update event log on completion' as Problem,
       suser_sname(sj.owner_sid ) as Owner,
       replace(replace(sj.name,char(10),' - '),char(13),' ') as Job_Name,
       replace(replace(sj.[Description],char(10),' - '),char(13),' ') as Job_Description,
       sj.[enabled],
    sj.notify_email_operator_id,
    sj.notify_level_eventlog,
    sj.notify_level_email
from
       msdb.[dbo].[sysjobs]                          sj
where
 sj.notify_level_eventlog not in
 (
  2 -- failure
  ,3 -- completion
 )
order by
       [enabled] desc,
       sj.name

/*
-- update event log on completion (i.e. success AND failure)
 update msdb.[dbo].[sysjobs]
 set notify_level_eventlog  = 3  -- on completion
 where notify_level_eventlog in
 (
  0 --not set
  ,2 -- on failure
 )
*/



#4 Who you gonna call?

You really don't want the job to fail and nobody knows about it, do you?
Oh, you check every day?  Every single job on every single server?  Even during your vacations?  Well good for you. 

I don't.  I get SQL Server  to send an email to the DBAdmin team if anything falls over
The exception is the Heartbeat Monitor


-- 4 - Jobs which are not set to email on failure
-- DBAdmin Heartbeat Monitor should email on success (1)
--**************************************************************
select
  '4 - not set to email on failure',
       suser_sname(sj.owner_sid ) as Owner,
       replace(replace(sj.name,char(10),' - '),char(13),' ') as Job_Name,
       replace(replace(sj.[Description],char(10),' - '),char(13),' ') as Job_Description,
       sj.[enabled],
    sj.notify_email_operator_id,
    sj.notify_level_eventlog,
    sj.notify_level_email
from
       msdb.[dbo].[sysjobs]                          sj
where
 sj.notify_level_email not in
 (
  2 -- failure
  ,3 -- completion
 )
order by
       [enabled] desc,
       sj.name