Stored procedure: Keep track of SQL Server 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.

A SQL Agent job is a complex database object with many details involved in the definition. There are even more details to consider while a job is actually running, but it's not convenient to examine the details for SQL Agent jobs using Enterprise Manager and the filtering is less than ideal.

The SQL code in Listing 1 creates a system stored procedure named sp_ListJobInformation. The routine lists schedule and status information about selected SQL Agent jobs.

The sp_ListJobInformation stored procedure accepts seven parameters, but none of them are required.

The first parameter (@DBUltra) is optional and it specifies whether to limit the list to only enabled jobs. A value of zero (0) means all eligible jobs should be listed. A value of one (1) means only enabled jobs should be listed.

The next parameter (@PCUltra) is optional and it specifies whether to limit the list to only running jobs. A value of zero (0) means all eligible jobs should be listed. A value of one (1) means only running jobs should be listed.

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 last parameter (@PCAdmin) is opt...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Database Development Speed up reports in SQL Server Reporting Services with caching Data Transformation Services vs. SSIS: The key differences Working with IntelliSense in SQL Server 2008 Management Studio Top tips and tricks for SQL Server database development Managing the development lifecycle with Visual Studio Team System 2008 Processing XML files with SQL Server functions A first look at Visual Studio Team System 2008 Database Edition How to create a SQL inner join and outer join: Basics to get started New datetime data types in SQL Server 2008 offer flexibility Using DATEADD and DATEDIFF to calculate SQL Server datetime values SQL Server Stored Procedures SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Stored procedures, triggers and SSRS reports Top tips and tricks for SQL Server database development Top 10 SQL Server development tips of 2008 SQL Server trigger vs. stored procedure to receive data notification SQL Server errors, failures and other problems fixed from the trenches SQL Server and data manipulation in T-SQL How to use SQL Server 2008 hierarchyid data type SQL Server stored procedures tutorial: Write, tune and get examples Check SQL Server database and log file size with this stored procedure SQL/Transact SQL (T-SQL) SQL language crash course (just enough to be dangerous) Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Working with sparse columns in SQL Server 2008 Determining the source of full transaction logs in SQL Server New GROUP BY option provides better data control in SQL Server 2008 Using the OPENROWSET function in SQL Server Loading data files with SQL Server's BULK INSERT statement Importing and exporting bulk data with SQL Server's bcp utility Testing transaction log autogrowth behavior in SQL Server SQL/Transact SQL (T-SQL) Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary library  (SearchSQLServer.com) trigger  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

ional and it specifies the character string used to identify a connection initiated to run a job. The default value of "SQLAgent%Job%" works for SQL Server 2000 and it should work for other versions as well, but this parameter allows for potential differences.

The sp_ListJobInformation stored procedure returns schedule and status information about SQL Agent jobs. There is one row per job and the rows are filtered according to @DBUltra, @PCUltra 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.

[IMAGE]
Query Analyzer Customize option

This example lists information for all currently running jobs.
EXECUTE sp_ListJobInformation 0,1

This example lists information for all enabled jobs, which means the jobs eligible for scheduled execution.
EXECUTE sp_ListJobInformation 1,0

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

Click for the stored procedure: sp_ListJobInformation

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