Problem solve Get help with specific problems with your technologies, process and projects.

Stored procedure: Examining SQL Agent job run history

Using Enterprise Manager to examine SQL Agent job run history is a chore. The sp_ListJobRunHistory stored procedure provides handy filtering features to make it easy.

This tip continues the system stored procedure series with a routine to list run history for selected SQL Agent...

jobs.

One of the many things a SQL Server database administrator needs to keep track of is SQL Agent jobs -- defined tasks of a relatively low priority that execute in parallel with normal operations. SQL Agent jobs can be invoked directly, but they are often invoked indirectly by setting them up with a schedule for execution. The SQL Agent component of SQL Server automatically runs the jobs according to their schedules.

Each time a SQL Agent job runs a log entry is made. The accumulated log entries form the job's run history, which includes the dates and times of execution, as well as status messages that describe the results. It's not very convenient to examine the run history using Enterprise Manager and there's no capability to filter the log entries.

The SQL code in Listing 1 creates a system stored procedure named sp_ListJobRunHistory. The routine lists specified run history records for selected SQL Agent jobs.

The sp_ListJobRunHistory stored procedure accepts six parameters, but none of them are required.

The first parameter (@DBAdmin) is optional and it specifies the maximum number of history records to return for each job. A value of zero (0) is the default and it means up to 100 records will be returned for each job. The actual number of records returned for each job may be further limited by the available history and by the other parameters.

The next parameter (@DBUltra) is optional and it specifies whether to include history for job runs that finished successfully. A value of zero (0) includes history for job runs that finished with any status. A value of one (1) excludes history for job runs that finished successfully.

The next four parameters are optional and they work together to form a combination of selection criteria using job names. Please refer to my earlier tip for an explanation of how these parameters work. These parameters specify the SQL Agent jobs to be considered.

The sp_ListJobRunHistory stored procedure returns the most recent history records, up to the number indicated by @DBAdmin (or the number that actually exist). There is one row per job and the rows are filtered according to @DBUltra and the other parameters.

I recommend using the Customize option under the Tools menu of Query Analyzer to set up an appropriate call of this stored procedure so it can be executed with a simple keyboard combination. This


screen image demonstrates the suggestion.

This example lists up to five history records for each job, but only if they ended without success.

EXECUTE sp_ListJobRunHistory 5,1

This example lists the most recent history record for each job, regardless of its execution status.

EXECUTE sp_ListJobRunHistory 1,0

I hope you find this system stored procedure to be useful.


Click for the stored procedure: sp_ListJobRunHistory


About the author: Brian Walker is a senior database architect in an IS department that uses SQL Server 2000 and the .NET Framework. He has more than 25 years of experience in the IT industry with the last several years focused on databases and SQL Server. Walker is a software developer, database developer, database administrator and database consultant. He develops utility software as a hobby, including a large collection of SQL Server utilities.


More information from SearchSQLServer.com

 

This was last published in December 2005

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close