Scheduled Task Reporting on SQL Server
April 30, 2003
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:
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:
The finished script
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?