Checking Job History
This article continues the same topic of Automatically Gathering
Server Information. If you want to read the previous articles, click on one of these links:
Getting a Count of Database Users – Part 1
Automatically Gather Database Size – Part 2
Automatically Gathering Log Space – Part 3
Getting Organized – Part 4
A Daily Report – Part 5
Capturing Ad Hoc Information – Part 6
Introduction
I have been gathering lots of information about my SQL Servers and then using
this to generate a daily report. As I began to gather this information, I have
chronicled my efforts in this series and the last couple columns have dealt with
the reporting of this information. I am now ready to expand the types of information
begin gathered with another set of stored procedures.
Job History
I make pretty heavy use of the SQLAgent in SQL Server 7 (and used SQL Executive in v6.5)
to run a variety of tasks every night. When tasks fail, I get notifications for most
of the tasks, but not all. Some are not worth getting a page or an email when they fail.
Especially the ones that run once a day and gather information or
I quickly realized from my initial reports that the tracking of the job history
for many of my scheduled tasks was missing. I also realized that I needed this
information in an easy to read format.
The Solution
If you have read my other articles, then you know I hate to write code that uses
unsupported or undocumented methods of solving a problem. This is mainly because I
have had code break during upgrades and had to rewite processes because Microsoft
changed the way an unsupported function worked. The code that I have written using
supported methods has continued to work through upgrades.
Having repeated this, I examined the method that I used in v6.5 to find out about
job history. I hate to say it, but the code was written in 1998 and used the system
tables in msdb to get job history. I decided to improve this and search BOL for another
method. My apologies to those of you who still run v6.5; you are on your own as I am not
sure this solution will work for v6.5.
In BOL I found quite a few system stored procedures that allow you to work with
the SQL Agent and get information about the scheduled tasks. I settled on sp_help_job_history.
While there may be better procedures, this one worked great for me. I am using BOL for SQL
Server 2000 beta and found the documentation for this procedure to be very complete and
useful.
The code I wrote uses some of the techniques that I have presented in earlier articles
as well as some of the objects. Specifically, I create a temporary table in the procedure
and then execute the system stored procedure with an insert [table] exec [procedure] technique that I used in Part 2. From this temporary table I then extract the information
that I need and insert it into the DBARpt table. The code for the procedure is here:
There are two calls to sp_help_job_history, one to return those jobs who returned a
failed status code and one to get jobs that returned an unknown code. There are a number of other
codes available; see BOL for more details.
Conclusions
The process I have developed here is one that I really like. It only alerts me to
failed jobs and I can assume all other jobs succeeded. I think this is much easier to
use than trying to weed through all results to find those that may have failed.
If you notice, this procedure checks the job history and then builds the report by
insering the information directly into DBARpt. Since the job history is stored in
the msdb database, unlike the disk space history that is not kept anywhere, I
decided to have the procedure directly build its report. I can always query Enterprise
Manager for the history prior to this days.
I hope that this article has provided some useful information for you. As always, I
welcome feedback and please take the time to rate this article below.
Steve Jones
March 2001
