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:

freq_type

Tells us if the job runs daily, weekly,
monthly etc.

freq_interval

Dependant on the freq_type, this
tells us which days of the week (weekly schedule) or month (monthly schedule)
the job runs on

freq_subday_type

Used when a job runs every few minutes or
hours

freq_subday_interval

Used when a job runs every few minutes or
hours

freq_relative_interval

Used when the job is scheduled to run on,
for example, the first or last day of every month

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

Neil Boyle
Neil Boyle
Neil Boyle left school at the age of sixteen thinking that computers were things that only existed in Star Trek. After failed careers as a Diesel Mechanic, Industrial Cleaner, Barman and Bulldozer Driver he went back to college to complete his education. Since graduating from North Staffs Poly he has worked up through the ranks from Trainee COBOL Programmer to SQL Server Consultant, a role in which he has specialised for the past seven years.

Latest Articles