Scheduled Task Reporting on SQL Server

April 30, 2003

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