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

SQL Agent Job Schedules

Many moons ago, I posted a script to help report on the human friendly scheduling sched_reportinformation for SSRS subscriptions. You can read all about that here.

In a very similar fashion, today I want to share how easy it is to turn the internal schedules for SQL Agent jobs into something that is more human friendly. SQL Agent job schedules should be easy to read for humans if for nothing else than the fact that you have to provide this information to the auditors from time to time. We all know they can’t properly interpret the numeric job schedules and properly determine what they mean.

The job schedules work quite well with the current design – which is not human friendly. The use of numbers is a streamlined approach and far more efficient. It may be a bit more useful to us if there were some sort of translation table that was stored. A stored translation table would make it terribly easy to figure out what is happening within the schedules for the SQL Agent jobs. We don’t have that, so we often have to do a little trial and error. Or we rely on people that have done that trial and error – with a little verification on our part. Afterall, these scripts are posted on the internet.

SQL Agent Job Schedules

I am not going to break down the script. With the similarity to the aforementioned SSRS script, I would recommend looking there for a little bit of an explanation. Here is the script to retrieve SQL Agent Job Schedules from your MSDB instance.

USE msdb;
GO
DECLARE @weekDay TABLE
	(
		mask INT
		, maskValue VARCHAR(32)
	);

INSERT INTO @weekDay
		SELECT 1
				, 'Sunday'
		UNION ALL
		SELECT 2
				, 'Monday'
		UNION ALL
		SELECT 4
				, 'Tuesday'
		UNION ALL
		SELECT 8
				, 'Wednesday'
		UNION ALL
		SELECT 16
				, 'Thursday'
		UNION ALL
		SELECT 32
				, 'Friday'
		UNION ALL
		SELECT 64
				, 'Saturday';

WITH	myCTE
			AS ( SELECT sched.name AS 'scheduleName'
							, sched.enabled AS SchedEnabled
							, sched.schedule_id
							, jobsched.job_id
							, CASE	WHEN sched.freq_type = 1 THEN 'Once'
									WHEN sched.freq_type = 4
											AND sched.freq_interval = 1
									THEN 'Daily'
									WHEN sched.freq_type = 4
									THEN 'Every '
											+ CAST(sched.freq_interval AS VARCHAR(5))
											+ ' days'
									WHEN sched.freq_type = 8
									THEN REPLACE(REPLACE(REPLACE(( SELECT maskValue
																FROM @weekDay AS x
																WHERE sched.freq_interval
																& x.mask <> 0
																ORDER BY mask
																FOR
																XML RAW
																),
																'"/><row maskValue="',
																', '),
															'<row maskValue="',
															''), '"/>', '')
											+ CASE	WHEN sched.freq_recurrence_factor <> 0
															AND sched.freq_recurrence_factor = 1
													THEN '; weekly'
													WHEN sched.freq_recurrence_factor <> 0
													THEN '; every '
															+ CAST(sched.freq_recurrence_factor AS VARCHAR(10))
															+ ' weeks'
												END
									WHEN sched.freq_type = 16
									THEN 'On day '
											+ CAST(sched.freq_interval AS VARCHAR(10))
											+ ' of every '
											+ CAST(sched.freq_recurrence_factor AS VARCHAR(10))
											+ ' months'
									WHEN sched.freq_type = 32
									THEN CASE	WHEN sched.freq_relative_interval = 1
												THEN 'First'
												WHEN sched.freq_relative_interval = 2
												THEN 'Second'
												WHEN sched.freq_relative_interval = 4
												THEN 'Third'
												WHEN sched.freq_relative_interval = 8
												THEN 'Fourth'
												WHEN sched.freq_relative_interval = 16
												THEN 'Last'
											END
											+ CASE	WHEN sched.freq_interval = 1
													THEN ' Sunday'
													WHEN sched.freq_interval = 2
													THEN ' Monday'
													WHEN sched.freq_interval = 3
													THEN ' Tuesday'
													WHEN sched.freq_interval = 4
													THEN ' Wednesday'
													WHEN sched.freq_interval = 5
													THEN ' Thursday'
													WHEN sched.freq_interval = 6
													THEN ' Friday'
													WHEN sched.freq_interval = 7
													THEN ' Saturday'
													WHEN sched.freq_interval = 8
													THEN ' Day'
													WHEN sched.freq_interval = 9
													THEN ' Weekday'
													WHEN sched.freq_interval = 10
													THEN ' Weekend'
												END
											+ CASE	WHEN sched.freq_recurrence_factor <> 0
															AND sched.freq_recurrence_factor = 1
													THEN '; monthly'
													WHEN sched.freq_recurrence_factor <> 0
													THEN '; every '
															+ CAST(sched.freq_recurrence_factor AS VARCHAR(10))
															+ ' months'
												END
									WHEN sched.freq_type = 64 THEN 'StartUp'
									WHEN sched.freq_type = 128 THEN 'Idle'
								END AS Frequency
							, ISNULL('Every '
										+ CAST(sched.freq_subday_interval AS VARCHAR(10))
										+ CASE	WHEN sched.freq_subday_type = 2
												THEN ' seconds'
												WHEN sched.freq_subday_type = 4
												THEN ' minutes'
												WHEN sched.freq_subday_type = 8
												THEN ' hours'
											END, 'Once') AS SubFrequency
							, REPLICATE('0', 6 - LEN(sched.active_start_time))
							+ CAST(sched.active_start_time AS VARCHAR(6)) AS start_time
							, REPLICATE('0', 6 - LEN(sched.active_end_time))
							+ CAST(sched.active_end_time AS VARCHAR(6)) AS end_time
							, REPLICATE('0', 6 - LEN(jobsched.next_run_time))
							+ CAST(jobsched.next_run_time AS VARCHAR(6)) AS next_run_time
							, CAST(jobsched.next_run_date AS CHAR(8)) AS next_run_date
						FROM msdb.dbo.sysschedules AS sched
							INNER JOIN msdb.dbo.sysjobschedules AS jobsched
								ON sched.schedule_id = jobsched.schedule_id
						WHERE sched.enabled = 1
				)
	SELECT j.name AS JobName
			, j.enabled
			, j.category_id
			, sp.name AS JobOwner
			, c.name
			, c.category_class
			, js.step_id
			, js.step_name
			, js.subsystem
			, js.command
			, js.database_name
			, js.database_user_name
			, ct.next_run_date
			, ct.next_run_time
			, ct.start_time
			, ct.end_time
			, ct.Frequency
			, ct.SubFrequency
			, ct.scheduleName AS ScheduleName
			, ct.SchedEnabled--, ss.
		FROM dbo.sysjobs j
			INNER JOIN dbo.sysjobsteps js
				ON j.job_id = js.job_id
			INNER JOIN dbo.syscategories c
				ON j.category_id = c.category_id
			INNER JOIN sys.server_principals sp
				ON j.owner_sid = sp.sid
			INNER JOIN myCTE ct
				ON ct.job_id = j.job_id;

This script will only retrieve the SQL Agent Job schedules that are not SSRS related and that are not SSIS maintenance package related. The SSRS stuff has already been introduced, and a quick eval of those blog posts will show the differences between grabbing SSRS information vs. grabbing just SQL Agent Jobs.

Stay tuned. I will post a script for the SSIS related scheduling information.

Now, go forth and audit your agent jobs. Document the schedules in an easy to read format. Give that report / document to the auditors and your manager and look like a rock star for the day!

Comments

Leave a comment on the original post [jasonbrimhall.info, opens in a new window]

Loading comments...