Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Apr 30, 2003

Scheduled Task Reporting on SQL Server

By Neil Boyle

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM