Scheduled Task Reporting on SQL Server

Introduction

If you have a server with a large number of
scheduled tasks, you will know that keeping track of them all can be a bit
difficult at times – checking and double-checking that your database backups
and log backups are in synch, that maintenance tasks go off in the correct
order, and that tasks do not interfere with one another by their timings, can
be a chore.

In Enterprise Manager, it is pretty hard to
keep track of a large number of scheduled tasks, so I have produced a script
that generated a report of all the scheduled tasks on a server. In looking at
how it works, we will gain an insight into the MSDB structure and the
scheduling engine.

MSDB (Microsoft Scheduling Database) is
installed by default in all versions of SQL Server, and is used by the SQL
Agent (SQL Executive in older versions) service to store details of all your
scheduled tasks.

For the purposes of this article, there are
two main tables in the MSDB we are going to look at: sysjobs and sysjobschedules.
(There are other useful tables in there, but these two will do nicely for this
article). Note that despite the “sys” prefix, these are actually user tables,
and as such have no “Information_schema” equivalent.

Sysjobs,
unsurprisingly, holds basic information about each job, such as its name, date
created, version number and so on.

sysjobschedules holds the details of when each job is supposed to run. This is a one:many
relationship – each job may have zero, one or more than one schedule associated
with it.

Both tables contain an “enabled” flag. The sysjobs
table holds the “master” flag and if the job is disabled none of the schedules
will run for that job even if they themselves are enabled. If the job is
enabled, only enabled schedules for that job will execute.

Sysjobs is
pretty straightforward. The fun and games for this little project start with sysjobschedules,
which needs a truckload of bitwise manipulation to decipher. I don’t propose to
detail everything here because this web page on the
subject does the job very well indeed.

Armed with that information, and a bunch of
CASE statements, we can begin to generate our report. All the basic information
is covered by this simple query:

select	*

from	sysjobs j

join	sysJobSchedules s

on	j.job_id = s.job_id

However, what comes out is not exactly a
readable report, and if your manager had asked you to document all the
scheduled tasks on a server, they would probably not be too impressed with
that. So we need to translate the data raw into a more human-friendly form, and
to do this we will manipulate the following sysjobschedules columns:

For example

If a job had a freq_type of 8 (Weekly) this
would indicate a weekly schedule. freq_interval is then used as a bitmap
indicating the days of the week the job runs on (Bit 1 is day 1, bit 2 is day 2
and so on…)

A snippet of code from the final SP shows
how we handle this

case freq_type

	when 1    then ‘Once’

	when 4    then ‘Daily’

	when 8    then ‘Wk ‘

	+    case      freq_interval & 2 when 2 then ‘M’ else ” end

	+    case      freq_interval & 4 when 4 then ‘Tu’ else ” end

	+    case      freq_interval & 8 when 8 then ‘W’ else ” end

	+    case      freq_interval & 16 when 16 then ‘Th’ else ” end

	+    case      freq_interval & 32 when 32 then ‘F’ else ” end

	+    case      freq_interval & 64 when 64 then ‘Sa’ else ” end

	+    case      freq_interval & 1 when 1 then ‘Su’ else ” end

	when 16   then ‘Mthly on day ‘ + convert(varchar(2), freq_interval)

If freq_subday_type
is set to 4 (indicating units in minutes) and freq_subday_interval is set to 2,
this indicates the job runs every two minutes.

,    case freq_subday_type

          when 1    then ‘Runs once at:’

          when 2    then ‘every ‘ + convert(varchar(3), freq_subday_interval) + ‘ seconds’

          when 4    then ‘every ‘ + convert(varchar(3), freq_subday_interval) + ‘ minutes’

          when 8    then ‘every ‘ + convert(varchar(3), freq_subday_interval) + ‘ hours’

     end as frequency

By padding our simple query out with a few
CASE statements that translate the binary data in sysjobschedules into a friendlier
format, we can aim to produce a scheduled task report looking more like this:

job_name               schedule                       frequency         start_at end_at

———————- —————————— —————– ——– ——–

Block_watcher          Daily                          every 2 minutes   00:00:00 23:59:59

Daily_log_backup       Wk MTuWThSaSu                  Runs once at:     20:00:00 NULL

Monthly_cleardown      Mthly Every Last Day           Runs once at:     00:00:00 NULL

monthly_maintenance    Mthly on day 1                 Runs once at:     02:30:00 NULL

Morning_and_evening    Daily                          Runs once at:     07:00:00 NULL

Morning_and_evening    Daily                          Runs once at:     23:00:00 NULL

weekly_DBCC            Wk Su                          Runs once at:     22:30:00 NULL

Weekly_full_backup     Wk F                           Runs once at:     20:00:00 NULL

(8 row(s) affected)

Here we have detailed for each job:

• Job name
• Schedule for the job as shorthand (i.e. TuThSa indicates Tuesday, Thursday & Saturday)
• The frequency of the job
• The start time for each job
• The end time for any recurring job

The finished script

Scheduled_Task_Reporter.sql

Feel free to play about with the script.
There are plenty of tweaks you could make to the formatting, and plenty of
features you could add. Why not try adding or checking the “enabled” status for
jobs and schedules for example?

»


See All Articles by Columnist Neil Boyle