Executive Summary:
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. |
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 here. (97393.zip) I wrote the procedure for use
on SQL Server 2005.
—Eric Peterson, President,
Peterson American Consulting