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
