SQL Agent Job Frequency Intervals in Plain English

A DBA I was talking to was trying to create a simple process that finds each SQL Agent Job on a given instance, extracts the basic information regarding it, and compiles a list that can be presented to non-DBAs for information about what sort of “undiscovered” processes are running in a given environment.  He had gotten a lot of the information compiled, but was having a problem dissecting the Job Schedule “Frequency Interval” field.  Now, to anyone who is familiar with binary, this is a fairly straight-forward bit-flag usage of a higher-order data-type.  But, not all DBAs have this in their background, and I think many could benefit from seeing it spelled out.

In the SQL Agent, you have the ability to have week-based job schedules, in which, you can specify particular days of the week.  For example, you may have a weekly job that performs your FULL backups, that only runs on Saturday, and then have your DIFFs run on Monday, Tuesday, Wednesday, Thursday, Friday, and Sunday.  Well, SQL Agent stores this sort of information in a single field as an integer.  This may seem awkward, but the logic behind it is very straight-forward.  Each of the lowest-order 7 bits represents a yes/no flag for a particular day of the week:

SQLAgentIntervalFrequenceBinaryExplaination
So, now that we have examined what this integer really means, the question naturally follows: “How do we represent this integer as a series of days?” I have written two different pieces of code to accomplish this. The first simply takes an integer at the beginning of the script and returns a simple string containing the days represented by that integer (this could easily be turned into a UDF or stored procedure). The second piece of code goes about things a little differently, but works well for the particular use case that we were originally discussing.

The first T-SQL:

SET NOCOUNT ON;
DECLARE @numberToTest   TINYINT = 119,
		@weekString     CHAR(21) = 'SunMonTueWedThuFriSat',
		@resultString	VARCHAR(35) = ''
 
IF OBJECT_ID('TempDB..#daysOfWeek') IS NOT NULL
	DROP TABLE #daysOfWeek
 
CREATE TABLE #daysOfWeek
(
[dayNumber]   TINYINT,
[dayCode]     TINYINT
)
 
INSERT INTO
	[#daysOfWeek]
	(
	[dayNumber],
	[dayCode]
	)
VALUES
	(1,1),
	(2,2),
	(3,4),
	(4,8),
	(5,16),
	(6,32),
	(7,64)
 
SELECT
	@resultString = @resultString + CASE
						WHEN (@numberToTest & [dayCode]) = [dayCode] THEN
							SUBSTRING(@weekString,(([dayNumber] - 1) * 3 + 1),3) + ', '
						ELSE ''
					END
FROM
	[#daysOfWeek]                                    
 
-- Strip off the last comma
IF (LEN(@resultString) > 1)
	SET @resultString = LEFT(@resultString,LEN(@resultString) - 1)
 
SELECT
	@resultString

And, the second T-SQL:

SET NOCOUNT ON;
 
IF OBJECT_ID('TempDB..#daysOfWeek') IS NOT NULL
       DROP TABLE #daysOfWeek
 
CREATE TABLE #daysOfWeek
(
[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 ')
 
;WITH BaseData AS (
			SELECT
				[SYSJOB].[job_id],
				[SYSJOB].[name],
				[SYSSCH].[freq_interval],
				[DOW].[dayName]
			FROM
				[msdb].[dbo].[sysjobs] AS SYSJOB
 
					INNER JOIN [msdb].[dbo].[sysjobschedules] AS SYSJOBSCH ON
						[SYSJOB].[job_id] = [SYSJOBSCH].[job_id]
 
					INNER JOIN [msdb].[dbo].[sysschedules] AS SYSSCH ON
						[SYSJOBSCH].[schedule_id] = [SYSSCH].[schedule_id]
 
						LEFT OUTER JOIN [#daysOfWeek] AS DOW ON
							([SYSSCH].[freq_interval] & [DOW].[dayCode]) = [DOW].[dayCode]    
			)
SELECT
	[job_id],
	[name],
	REPLACE(REPLACE([DayNames],'',''),'','') AS DayNames
FROM
	(
	SELECT
		[job_id],
		[name],
		(
		SELECT
			[dayName]
		FROM
			[BaseData] 
		WHERE
			[job_id] = [Grouped].[job_id]
		FOR XML PATH ('')
		) AS DayNames
	FROM
		[BaseData] AS Grouped
	GROUP BY
		[job_id],
		[name]
	) AS ConcatenatedData

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*