Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 19, 2001

Automated Server Information Gathering - Part 7

By Steve Jones

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


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.


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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM