Monday 19 March 2018


SQL server Agent runs jobs, and if they fail to run, it can be configured to send you a message to say they have fallen over.  You can if you like configure it to send a message if it succeeds too.    Beware!  That way lies madness. 

Problem with a Job Succeeded message is that there may be dozens, even hundreds of servers.  Each server might easily have dozens, even hundreds of jobs running on them.  Once a day, once an hour, once a minute even.  So for 50 servers running 25 jobs every hour on the hour, that is a lot of emails.  You will probably delete them automatically without reading them, and so miss the one you really care about - the one job that failed last night.

So set it up to notify you of jobs that fail. 

All well and good.  But how will you know if SQL Server Agent itself has failed?  It does, occasionally. 

I set up a job on each server which runs a Job Succeeded job on each server, once a day.  At midnight, before the more significant jobs run.  It sends me a message.  I never read it. 

But I have set up rules in my email which put the messages into folders, one per server.   The job almost always succeeds, and every day, the number of messages goes up by one. 

But if SQL Server Agent ever fails on any server, the number of messages will be different.  I can see at a glance that something has gone wrong

And I can go and fix it

No comments:

Post a Comment