SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to Decipher sysschedules

By Alan Jefferson,

I recently was given the assignment to discover everything that was happening on a client’s 3 SQL Servers. I started where you might expect by taking an inventory of all of the databases. I listed all of the files and their sizes and the options that are returned with sp_helpdb. Then I listed the tables, columns and how many rows each table had and continued to do some other helpful things along those lines.

Then I came to the jobs running on SQL Agent.

Listing the jobs and what they do was no problem. Just queried sysjobs and sysjobsteps and that was all I needed. Here is the query that I used to pull the job steps.

SELECT
    ServerName = @@ServerName,
    sj.name,
    sj.enabled,
    sjs.step_id,
    sjs.step_name,
    sjs.database_name,
    sjs.subsystem,
    sjs.command,
    sjs.last_run_outcome,
    last_run = CASE
                   WHEN LEN(sjs.last_run_date) = 8 THEN
                       CONVERT(
                                  DATETIME,
                                  CONVERT(VARCHAR(8), sjs.last_run_date)
                                  + '  '
                                  + LEFT(RIGHT('000000'
                                               + CONVERT(
                                                            VARCHAR(6),
                                                            sjs.last_run_time
                                                        ), 6), 2) + ':'
                                  + SUBSTRING(
                                                 RIGHT('000000'
                                                       + CONVERT(
                                                                    VARCHAR(6),
                                                                    sjs.last_run_time
                                                                ), 6),
                                                 3,
                                                 2
                                             ) + ':'
                                  + RIGHT('00'
                                          + CONVERT(
                                                       VARCHAR(6),
                                                       sjs.last_run_time
                                                   ), 2)
                              )
                   ELSE
                       NULL
               END
FROM
    sysjobs sj
    JOIN sysjobsteps sjs
        ON sj.job_id = sjs.job_id
ORDER BY
    sj.name,
    sjs.step_id;

The toughest part about the query above is the last_run field. I did some searching on the trusty internet and found a couple of articles that got it wrong. The last_run_time field is the time stored as an integer but instead of being some interval of time since midnight as some have stated, it is actually the digits of the time stored as an integer. For example, 0 is midnight. But 100 is 12:01:00 and 10000 is 1:00:00. So in order to convert the integer into time you must pad the value with leading zeros and then break it up into parts.

NOTE: I was using SQL 2008 R2. SQL 2012 includes the FORMAT function that would have allowed me to insert the colons and break up the digits in one step using the character mask ‘##:##:##’.

At this point I realized that I was going to have to convert several time values into text using the same logic above so I decided to write a function. In the process I also was able to clean it up a bit so that it displays the time in 12 hour format with no milliseconds and including the AM or PM. Here is the script for the function that will be used below.

CREATE FUNCTION fn_IntToTimeString (@time INT)
RETURNS VARCHAR(20)
AS
BEGIN
    DECLARE @return VARCHAR(20);
    SET @return = '';
    IF @time IS NOT NULL
       AND @time >= 0
       AND @time < 240000
        SELECT
            @return
            = REPLACE( CONVERT(VARCHAR(20), CONVERT(TIME, LEFT(RIGHT('000000'
                     + CONVERT(VARCHAR(6), @time), 6), 2)
                     + ':'
                     + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6), @time), 6), 3, 2) + ':'
                     + RIGHT('00' + CONVERT(VARCHAR(6), @time), 2)),109),'.0000000',' '
					 );
    RETURN @return;
END;

Undaunted by that task I moved on to tackle the hard part; how to report the frequency that each job is scheduled to run.  

SYSJOBSCHEDULES

First, jobs can have multiple schedules so there is an intersection table called SYSJOBSCHEDULES that contains a job_id and a schedule_id. Pretty straight forward; that is about all we need to find all of the schedules for each job.

SYSSCHEDULES

I used a couple of sources to decipher the sysschedules table. The first is BOL. It contains the definitions for the important fields that I would need. Let’s list the important ones here:

schedule_id: int, ID of the SQL Server Agent job.

name: nvarchar(128), The name of the schedule.

freq_type: int, How frequently a job runs for this schedule. Value is combined with freq_interval and freq_relative_interval to determine the days that the job is executed. See the table below.

Freq_type

Freq_interval

1 Once

Unused (0)

4 Daily

Recurs every freq_interval days

8 Weekly

1 = Sunday

2 = Monday

4 = Tuesday

8 = Wednesday

16 = Thursday

32 = Friday

64 = Saturday

16 Monthly

On the freq_interval day of the month

32 Monthly, relative (also uses freq_relative_interval)

Freq_relative_interval

1 = First week of the month

2 = Second week of the month

4 = Third week of the month

8 = Fourth week of the month

16 = Last week of the month


freq_interval

1 = Sunday

2 = Monday

3 = Tuesday

4 = Wednesday

5 = Thursday

6 = Friday

7 = Saturday

8 = Day (every day of the week)

9 = Weekday

10 = Weekend Day

64 Runs when SQL Agent starts up

Unused (0)

128 Runs when computer is Idle

Unused (0)

Freq_recurrence_factor: int, If Freq_Type is 8, 16, or 32, the job will run every freq_recurrence_factor Weeks, or Months.

Freq_subday_type: int, If the job runs multiple times per day Freq_subday_type describes the units to be combined with the freq_subday_interval. The valid values are:

  • 1 = Job runs once at the specified time
  • 2 = Seconds: Job runs every Freq_subday_type seconds
  • 4 = Minutes: Job runs every Freq_subday_type minutes
  • 8 = Hours: Job runs every Freq_subday_type hours

Active_start_date: int, The date that the job will begin in the form YYYYMMDD

Active_end_date: int, The date that the job will end in the form YYYYMMDD

Active_start_time: int, The time of day the job will start. It is the same logic described in the last_run_time discussion above

Active_end_time: int, The time of day the job will end. It is the same logic described in the last_run_time discussion above

Those are the main fields that are used to control the frequency that the job runs. Now let’s dive into the values and see how to convert into a text description. Notice that the values of freq_interval are powers of 2 when freq_type = 8 (Weekly) This is how the check boxes for each day of the week are stored. So we will need to parse the bits to decode which boxes are checked. I found a good article that describes this bitwise logic here: http://www.sqlphilosopher.com/wp/2013/01/sql-agent-job-frequency-intervals-in-plain-english/

In fact, the above article is the basis for the query that I wrote. You will notice similarities. I merely built on the script that I found there. That article got me pretty far along the process but it had to be repeated for freq_relative_interval when freq_type = 32. 

The first part of the script is to declare 4 table variables: @daysOfWeek, @daysOfWeek_relative, @weeksOfMonth and @ordinal. Each table has 3 fields; a Row number, a code for the lookup for the row and the text interpretation.

DECLARE @daysOfWeek TABLE
(
[dayNumber]   TINYINT,
[dayCode]     TINYINT,
[dayName]  VARCHAR(11)
)

INSERT INTO
      @daysOfWeek
      (
      [dayNumber],
      [dayCode],
  [dayName]
      )
VALUES
      (1,1, 'Sunday'),
      (2,2, 'Monday'),
      (3,4, 'Tuesday'),
      (4,8, 'Wednesday'),
      (5,16, 'Thursday'),
      (6,32, 'Friday'),
      (7,64, 'Saturday');

DECLARE @daysOfWeek_relative TABLE
(
[dayNumber] INT,
[dayCode]   INT,
[dayText] VARCHAR(250)
)

INSERT INTO
      @daysOfWeek_relative
      (
      [dayNumber],
      [dayCode],
      [dayText]
      )
VALUES
      (1,1, 'On the <<wk>> Sunday of every <<n>> Month(s)'),
      (2,2, 'On the <<wk>> Monday of every <<n>> Month(s)'),
      (3,3, 'On the <<wk>> Tuesday of every <<n>> Month(s)'),
      (4,4, 'On the <<wk>> Wednesday of every <<n>> Month(s)'),
      (5,5, 'On the <<wk>> Thursday of every <<n>> Month(s)'),
      (6,6, 'On the <<wk>> Friday of every <<n>> Month(s)'),
      (7,7, 'On the <<wk>> Saturday of every <<n>> Month(s)'),
      (8,8, 'Each Day of the <<wk>> week of every <<n>> Month(s)'),
      (9,9, 'Each Weekday of the <<wk>> week of every <<n>> Month(s)'),
      (10,10, 'Each Weekend Day of the <<wk>> week of every <<n>> Month(s)');

DECLARE @weeksOfMonth TABLE
(
[womNumber]   TINYINT,
[womCode]     TINYINT,
[womName]  VARCHAR(11)
)

INSERT INTO
      @weeksOfMonth
      (
      [womNumber],
      [womCode],
      [womName]
      )
VALUES
(1, 1, 'First'),
(2, 2, 'Second'),
(3, 4, 'Third'),
(4, 8, 'Fourth'),
(5, 16, 'Last');

DECLARE @Ordinal TABLE
(OrdinalID int,
OrdinalCode int,
OrdinalName varchar(20))

insert into @Ordinal (OrdinalID, OrdinalCode, OrdinalName)
values
(1,1,'1st'),
(2,2,'2nd'),
(3,3,'3rd'),
(4,4,'4th'),
(5,5,'5th'),
(6,6,'6th'),
(7,7,'7th'),
(8,8,'8th'),
(9,9,'9th'),
(10,10,'10th'),
(11,11,'11th'),
(12,12,'12th'),
(13,13,'13th'),
(14,14,'14th'),
(15,15,'15th'),
(16,16,'16th'),
(17,17,'17th'),
(18,18,'18th'),
(19,19,'19th'),
(20,20,'20th'),
(21,21,'21st'),
(22,22,'22nd'),
(23,23,'23rd'),
(24,24,'24th'),
(25,25,'25th'),
(26,26,'26th'),
(27,27,'27th'),
(28,28,'28th'),
(29,29,'29th'),
(30,30,'30th'),
(31,31,'31st');

A couple of things to notice; first, the @daysOfWeek and @weeksOfMonth table code fields correspond to the bit mask that I mentioned above that comes from freq_interval and freq_relative_interval respectively. We will use that in the next part of the script to create a comma separated list for the days of the week and the weeks of the month that the job is scheduled to run. Second the @daysOfWeek_relative table contains some tags (<<wk>>, <<n>>) that will be replaced with text values later in the script. I did it this way because the sentence structure wasn’t quite the same for all of the schedule descriptions but they all used the same basic information. This allowed me to insert the variable text in the right spot for each sentence type.

Now let’s look at how to convert the freq_Interval and freq_relative_interval values from a bit mask into a comma separated list of days and weeks. I did this with two CTEs. One called CTE_DOW for the days of the week and another called CTE_WOM for the weeks of the month.

WITH CTE_DOW
AS (SELECT DISTINCT
        schedule_id,
        Days_of_Week = CONVERT(VARCHAR(250), STUFF(
                                  (
                                      SELECT ', ' + DOW.dayName
                                      FROM @daysOfWeek DOW
                                      WHERE
                                          ss.freq_interval & DOW.dayCode = DOW.dayCode
                                      FOR XML PATH('')
                                  ), 1, 2, '')
                              )
    FROM msdb.dbo.sysschedules ss
   ),
CTE_WOM
AS (SELECT DISTINCT
        schedule_id,
        Weeks_of_Month = CONVERT(VARCHAR(250), STUFF(
                                    (
                                        SELECT ', ' + WOM.womName
                                        FROM @WeeksOfMonth WOM
                                        WHERE
                                            ss.freq_relative_interval
                                            & WOM.womCode = WOM.womCode
                                        FOR XML PATH('')
                                    ), 1, 2, '')
                                )
    FROM msdb.dbo.sysschedules ss
   )
;

Each Common Table Expression does essentially the same thing with different fields. The two big takeaways from this part of the script are the ‘&’ bitwise AND operator and the for xml path. As mentioned above the sqlphilosopher.com article does a good job of explaining how that works so I won’t include that here but the for xml path(‘’) is necessary to pivot the data from rows to a comma separated list. This is basically done by creating an XML string with no tags and building the comma separator into each value of the list. Also note that the STUFF command strips off first two characters which will be a leading comma and a space. If you are working with SQL 2005 you can use a substring instead.  

Now we get to the heart of the script where we put it all together.

SELECT
    Server_Name = @@ServerName,
    Job_Name = sj.name,
    Job_Enabled = sj.enabled,
    Schedule_Name = ss.name,
    Schedule_Enabled = ss.enabled,
    Frequency = CONVERT(VARCHAR(500), CASE freq_type
										WHEN 1 THEN 'One Time Only'
										WHEN 4 THEN 'Every ' + CONVERT(VARCHAR(3), ss.freq_interval) + ' Day(s)'
										WHEN 8 THEN 'Every ' + ISNULL(DOW.Days_of_Week, '')
												+ ' of every '
												+ CONVERT(VARCHAR(3), ss.freq_recurrence_factor ) + ' Week(s).'
										WHEN 16 THEN 'On the ' + ISNULL(od.OrdinalName, '') 
										        + ' day of every '
												+ CONVERT(VARCHAR(3), ss.freq_recurrence_factor ) + ' Month(s).'
										WHEN 32 THEN REPLACE(REPLACE(DOWR.dayText, '<<wk>>', ISNULL(WOM.Weeks_of_Month,'')),'<<n>>',
                                              CONVERT(VARCHAR(3), ss.freq_recurrence_factor))
                                        WHEN 64 THEN 'When SQL Server Starts'
										WHEN 128 THEN 'WHEN SQL Server is Idle'
										ELSE '' 
		                           END
                       ),
    Interday_Frequency = CONVERT(VARCHAR(500), CASE 
												WHEN freq_type NOT IN ( 64, 128 ) THEN
			                                            CASE freq_subday_type
						                                    WHEN 0 THEN ' at '
			                                                WHEN 1 THEN 'Once at '
			                                                WHEN 2 THEN 'Every ' + CONVERT(VARCHAR(10),ss.freq_subday_interval)
																		+ ' Second(s) starting at '
															WHEN 4 THEN 'Every ' + CONVERT(VARCHAR(10),ss.freq_subday_interval)
					                                                    + ' Minutes(s) starting at '
								                            WHEN 8 THEN 'Every '+ CONVERT(VARCHAR(10), ss.freq_subday_interval)
					                                                    + ' Hours(s) starting at '
								                            ELSE ''
                                            END
                                            + dbo.fn_IntToTimeString(active_start_time)
                                            + CASE
                                                  WHEN ss.freq_subday_type IN ( 2, 4, 8) THEN ' Ending at '
		                                                      + dbo.fn_IntToTimeString(active_end_time)
                                                  ELSE ''
                                              END
                                        ELSE ''
                                    END
                                ),
    active_start_date = CONVERT(DATETIME, CONVERT( VARCHAR(8), ss.active_start_date, 114 )),
    active_start_time = dbo.fn_IntToTimeString(active_start_time),
    active_end_date = CONVERT(DATETIME, CONVERT(VARCHAR(8), ss.active_end_date, 114)),
    active_end_time = dbo.fn_IntToTimeString(active_end_time)
FROM
    msdb.dbo.sysjobs sj
    JOIN msdb.dbo.sysjobschedules sjs
        ON sj.job_id = sjs.job_id
    JOIN msdb.dbo.sysschedules ss
        ON sjs.schedule_id = ss.schedule_id
    LEFT JOIN CTE_DOW DOW
        ON ss.schedule_id = DOW.schedule_id
    LEFT JOIN CTE_WOM WOM
        ON ss.schedule_id = WOM.schedule_id
    LEFT JOIN @Ordinal od
        ON ss.freq_interval = od.OrdinalCode
    LEFT JOIN @Ordinal om
        ON ss.freq_recurrence_factor = om.OrdinalCode
    LEFT JOIN @daysOfWeek_relative DOWR
        ON ss.freq_interval = DOWR.dayCode;

At the heart of the query are the two case statements for the Frequency and Interday_Frequency output fields. They interpret the freq_type and freq_interday_type values into the correct sentence structure. The tags that I mentioned above come into play in the Frequency field when freq_type = 32. I didn’t want to add yet another case statement at that point. I noticed that the same values were used in all of the descriptions just in different places so I was able to use two replace commands to insert the variable data in the place where it belonged.

I set up a couple of test schedules in the SQL Server Agent and compared the output of my script to the description that SQL provides to make sure it looked good to me and then worked through the different scheduling options to make sure the solution was complete. Below is a sample of what I was looking at when comparing the two.

You may notice that my descriptions are not identical to the ones generated by SQL Server, but I decided that my descriptions are close enough and they covered all of the options that SQL provides. I hope this helps the next time you have to do discovery on the jobs running on an unfamiliar server. The script should go a long way to seeing when things run at a glance. The code in this article is contained in the attachment decipher_sysschedules.zip

 

Resources:

decipher_sysschedules.zip
Total article views: 880 | Views in the last 30 days: 18
 
Related Articles
SCRIPT

Function for Getting Interval Dates by days,months,years.

Function for Getting Interval Dates by days,months,years for particular week number, for particular ...

FORUM

converting varchar to numeric

converting varchar to numeric

FORUM

convert varchar to numeric

convert varchar to numeric

FORUM

convert month name to month number

convert month name to month number

FORUM

convert the string value into varchar

convert the string value into varchar

Tags
 
Contribute