Automatically Gathering Server Information Part 7

Checking Job History

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

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:

dbspDBARpt_ChkJobHistory

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.

Since I try to build fault tolerance into the system, I do not depend on this process running every day. Instead, I use the technique discussed in Process Tracking to determine the last time I checked the job history and then return all the history information since that time. The code for the PrcssTrckr table is here:PrcssTrckr.sql.

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.

Return to Steve Jones Home