Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Tracking for Your SQL Server Agent Jobs
If you often have to keep track of Microsoft SQL Server Agent jobs, you'll probably find the cspJobMonitor procedure quite useful. This T-SQL procedure accesses the internal tables in SQL Server Agent and displays the summary results set. This procedure offers three modes that let you check the last run for all jobs, see all the jobs that ran in the past 24 hours, and get a historical look at all the runs that have occurred for a specific job.
Readers
November 19, 2007
3 Min Read
How often are you asked:
When was the last time this job was run?
Can I have a list of every time this job was run?
Is my job enabled?
What jobs will be affected if we bring down the system for maintenance?
Did our scheduled maintenance job run successfully last night?
As you probably know, answering these questions is more complicated than you might think. You either have to view the history for all the jobs or review the job properties for each job. SQL Server Agent stores the information, but you need to know the tables in which it’s stored.
I’ve written a procedure, cspJobMonitor, that accesses the internal tables in SQL Server and displays the summary results set. This procedure can provide answers to all the aforementioned questions and more. It works in three modes: LastRun, Last24, and NamedJob.
LastRun mode. When you use the cspJobMonitor procedure in the LastRun mode, you receive a list of the last run for all jobs as well as additional information about each of those jobs. Figure 1 shows sample output from this mode. As you can see, the additional job information includes whether the job is enabled, how long the last run took, and when the next run is scheduled. To use the procedure in the LastRun mode, you run the command
cspJobMonitor 'LastRun'Last24 mode. When you use the cspJobMonitor procedure in the Last24 mode, you receive a list of all the jobs that ran in the past 24 hours. Figure 2 shows sample output from the Last24 mode. Like in the LastRun output, the Last24 output includes additional information about each job. To use the procedure in the Last24 mode, you run the command
cspJobMonitor 'Last24'NamedJob mode. When you use the cspJobMonitor procedure in the NamedJob mode, you receive a list of all the runs that have occurred for the specified job. Figure 3 shows sample output from this mode. This historical information will be helpful to not only your boss but also Statement on Auditing Standards No. 70 (SAS 70) auditors. Before you use the NamedJob mode, though, you need to be aware of a few things about the output:
The "Enabled" and "Next Run" fields always show the results for the last run. For example, in the sample output in Figure 3, the "Next Run" field for the Backup DBS job that ran on 2007-05-06 lists the next run as 2007-05-08 and not 2007-05-07.
The sample output in Figure 3 lists only the total job (i.e., step 0) for the status. If you code a job to end with success or go to the next step when an error occurs, it will only show that the job completed and not that a single step failed.
The number of jobs shown is controlled by the setting in the history options in SQL Server Agent's Properties dialog box. By default, it's set at 1,000 rows, the history will be automatically deleted from the system when the total log size or job-limit specification is reached. If you have several jobs, reaching 1,000 rows doesn't take that long. Be careful—SAS 70 auditors like to see a full year's worth of historical data.
The output isn't a replacement for SQL Server Agent's job history log but rather a quick summary of jobs in the system in a readable format.
To use the procedure in the NamedJob mode, you run the command
cspJobMonitor 'job_name'where job_name is the name of the job for which you want to receive a history.
You can download the cspJobMonitor procedure above via the "Download the Code" icon. I wrote the procedure for use on SQL Server 2005.
—Eric Peterson, President,
Peterson American Consulting
About the Author
You May Also Like
.jpg?width=700&auto=webp&quality=80&disable=upscale)
