If you have decided to become a database administrator, then you better get used to people asking you … what happened last night?
Now, when someone first says that to you, you may be thinking, “I don’t know, I was sleeping”.
And as much as you would like to say that, that type of attitude and/or response won’t lead to a lasting career as a DBA. So, realistically, you have to be able to determine what happened on the SQL Server. Now, that is a complex subject, and in this post we are only going to cover one aspect … what happened with the SQL Server Agent jobs?
Checking the outcome of the jobs running on the SQL Server should really be one of the standard checks that you do on a daily basis for all the SQL Servers you administer.
Checking the Outcome of SQL Server Agent Jobs
Granted, there are many ways to make sure that you are aware of the outcomes of the SQL Server Agent jobs.
- Set notifications on each job – This is probably one of the most proactive approaches. Every time a job runs it will eventually finish with a status (succeeds, fails, or completes), from which you can be notified. This works well when you have a small number of jobs that don’t run that often. However, if you have jobs running every 5 minutes, you certainly don’t want to be notified every time it completes, perhaps just when it fails. (Notify an Operator of Job Status: http://msdn.microsoft.com/en-us/library/ms191130.aspx)
- Management Studio – This is a manual approach to solving the problem of checking the outcome of a job. Simply, use Management Studio to view the history of the jobs running on the server. If you have only a few servers (1 or 2), this may work fine. But, most DBA’s will probably manage quite a few servers, so doing a manual process like that will be very time-consuming and repetitive. (View the Job History: http://msdn.microsoft.com/en-us/library/ms181046.aspx)
- Programmatically – One of the cooler ways, which will help you understand some of the internals of SQL Server, would be to check the outcomes of jobs programmatically, using T-SQL. With T-SQL, you can use the Microsoft provided stored procedures, such as sp_help_jobhistory (http://msdn.microsoft.com/en-us/library/ms188025.aspx), or you could query some of the underlying tables in the msdb database directly (i.e. sysjobs, sysjobservers, sysjobhistory). (SQL Server Agent Tables: http://msdn.microsoft.com/en-us/library/ms181367.aspx) If you implement this approach, it will also make it easy to have a job summary report and even log this information to a database. Additionally, you could do most of this with PowerShell, and I will focus on that in a later post.
- Monitoring Software – Now, if you are fortunate enough to be employed by a company that will buy SQL Server monitoring software, then it can make your life quite a bit easier. Some of the ones I have used in the past that I have liked are RedGate’s SQL Monitor (http://www.red-gate.com/products/dba/sql-monitor/), Idera’s Diagnostic Manager (http://www.idera.com/SQL-Server/SQL-diagnostic-manager/), and SQL Sentry’s Event Manager (http://www.sqlsentry.net/event-manager/sql-server-enterprise.asp). At my current company, we are using Quest’s Foglight (http://www.quest.com/foglight-for-sql-server/), and I have not yet fully developed an opinion about it.
So, there you have it. A variety of ways to do the same thing, but they all come with trade-offs (i.e. time and expense). And don’t feel like you have to stick with just one of these approaches. I would suggest a combination. For instance, set all your jobs to notify you on a failure status. Also, programmatically collect all the statuses of your jobs and send yourself or your management a morning report. And if you can, buy some third-party monitoring software.
But, the most important thing is to implement one or a combination of these approaches. That way, when someone comes up to you and asks “what happened last night?”, you will already know and be able to give them an answer.