SQLGeeksPro

This article is to keep a handy script to list SQL Agent Jobs and Schedules. In the previous article I had provided  a script to change the job schedule. This script may be very next one you need.

Once we changed the job schedules in all SQL Servers in the environment, we needed a script to verify. There are many scripts available in the net. Hence I kind of tweaked one of the script. It’s been long time and hence not able to provide the original source.

Note:Please change the Job Names in the Where clause of the query to verify the job schedule of the jobs you made changes to.

Query to get list of jobs in sql server with schedules

SELECT	 [JobName] = [jobs].[name]
		,[Enabled] = CASE [jobs].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
		,[Scheduled] = CASE [schedule].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
		,[Occurs] = 
				CASE [schedule].[freq_type]
					WHEN   1 THEN 'Once'
					WHEN   4 THEN 'Daily'
					WHEN   8 THEN 'Weekly'
					WHEN  16 THEN 'Monthly'
					WHEN  32 THEN 'Monthly relative'
					WHEN  64 THEN 'When SQL Server Agent starts'
					WHEN 128 THEN 'Start whenever the CPU(s) become idle' 
					ELSE ''
				END
		,[Occurs_detail] = 
				CASE [schedule].[freq_type]
					WHEN   1 THEN 'O'
					WHEN   4 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' day(s)'
					WHEN   8 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' weeks(s) on ' + 
						LEFT(
							CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END , 
							LEN(
								CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END 
							) - 1
						)
					WHEN  16 THEN 'Day ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
					WHEN  32 THEN 'The ' + 
							CASE [schedule].[freq_relative_interval]
								WHEN  1 THEN 'First'
								WHEN  2 THEN 'Second'
								WHEN  4 THEN 'Third'
								WHEN  8 THEN 'Fourth'
								WHEN 16 THEN 'Last' 
							END +
							CASE [schedule].[freq_interval]
								WHEN  1 THEN ' Sunday'
								WHEN  2 THEN ' Monday'
								WHEN  3 THEN ' Tuesday'
								WHEN  4 THEN ' Wednesday'
								WHEN  5 THEN ' Thursday'
								WHEN  6 THEN ' Friday'
								WHEN  7 THEN ' Saturday'
								WHEN  8 THEN ' Day'
								WHEN  9 THEN ' Weekday'
								WHEN 10 THEN ' Weekend Day' 
							END + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)' 
					ELSE ''
				END
		,[Frequency] = 
				CASE [schedule].[freq_subday_type]
					WHEN 1 THEN 'Occurs once at ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 2 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Seconds(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 4 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Minute(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 8 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Hour(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					ELSE ''
				END
FROM	 [msdb].[dbo].[sysjobs] AS [jobs] WITh(NOLOCK) 
		 LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITh(NOLOCK) 
				 ON [jobs].[job_id] = [jobschedule].[job_id] 
		 LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITh(NOLOCK) 
				 ON [jobschedule].[schedule_id] = [schedule].[schedule_id] 
		where [jobs].[name] in ('1_DBA job Name', '11_DBA job Name') -- Change the job names or add/remove as applicable
GO

Again please change the values for Where clause. I know it is a long query hence mentioning it again.

The OutPut of the above query

The query provides details on how the jobs are scheduled. If you want to to list the schedules of all the SQL Agent Jobs then just remove the where clause.

This article is about a simple script to change sql agent job schedule. Now most you may be thinking why do you need a script while you can change it through SSMS.

But if you get an issue like sql server agent job is not running as per standard schedule. And the issue is on 100s of SQL Instances, you definitely prefer a query to do it on one shot.

Now let me tell you what made me develop this simple query which helped big time. Someone ran a script to create DBA maintenance job automatically.

Of course all went well until we realized two jobs were running thrice a week. The maintenance jobs should run on weekends only and hence the below query helps achieving the same.
You can use SQL Server’s Central Management Server (CMS) to execute the query in all SQL Instances at one shot.

Alter SQL Job Schedule Using Script

NOTE: Please don’t copy paste the query and run without reading the comments. You need to change few parameter values as per your requirements.

The below query is an example to change the job schedule of the SQL Agent Job “1_DBA job Name” to run Weekly Once on Saturday.

DECLARE @schid INT

SELECT @schid = sjc.schedule_id
FROM msdb..sysjobs AS sjob
INNER JOIN msdb..sysjobschedules AS sjc ON sjob.job_id = sjc.job_id
INNER JOIN msdb..sysschedules AS sch ON sjc.schedule_id = sch.schedule_id
WHERE sjob.name = '1_DBA job Name' -- Change The Job Name

EXEC msdb..sp_update_schedule @schedule_id = @schid
	,@freq_type = 8  --The value indicates when a job is to be executed. Refer Table_1
	,@freq_interval = 64 -- Refer Table_2 below and change it as per your requirement

freq_type: The value indicates frequency of a job is to be executed

freq_interval: The days that a job will execute.

Please refer the Microsoft article Microsoft Documentation to understand and use different schedule options.
If you have some basic T-SQL knowledge you must be thinking this could have been achieved just by running sp_update_schedule. Well that is true if you have unique schedule name for the SQL Agent Job you are trying to change the schedule. If not this simple query will help.

The very next step would be to verify the changes you just made. Here is a script to achieve the same.>>Continue

<< Previous
This article is in continuation of Script to Delete SQL Agent Jobs. If you have landed here directly you might want to check the above article if the first script helps.
Anyways in this continuation article I am going to provide you the script to Delete SQL Agent Jobs in a scenario when you need to delete all the jobs except the default one “syspolicy_purge_history”.

Script to Delete All SQL Agent Jobs except the default one:

DECLARE @SQLStatement VARCHAR(200)
DECLARE @jobName SYSNAME
   DECLARE c1 CURSOR FOR
   SELECT name FROM msdb.dbo.sysjobs_view WHERE name <> 'syspolicy_purge_history' 
   OPEN c1
   FETCH NEXT FROM c1 INTO @jobName
     IF @@CURSOR_ROWS=0
     PRINT 'No Job found! Please re-check LIKE operator.'
     WHILE @@fetch_status=0
    BEGIN
       SET @SQLStatement= 'EXEC msdb.dbo.sp_delete_job @job_name =''' + @jobName + ''', @delete_unused_schedule=1' 
       PRINT(@SQLStatement)
     --EXEC (@SQLStatement)   --Uncomment to Execute
       FETCH NEXT FROM c1 INTO @jobName
    END
   CLOSE c1
   DEALLOCATE c1

The result set will provide you with the SQL delete statements if you run the above query as is. All you need to do is copy the output and  execute them in the SSMS.

Sample result set as shown in the following screenshot:

There is another way to execute the query. You can directly delete the jobs without generating the delete statements. If you have little T-SQL knowledge you can modify the same query to do it. If you are an accidental DBA, no worries just remove the -- sign from the statement EXEC (@SQLStatement) and comment the statement PRINT(@SQLStatement). I have explained this already in the previous article so if you can refer that.

Now let’s talk about a scenario when you need to delete all sql agent jobs except the replication, mirroring, log shipping or except few jobs.
Yes, you got it right. All you need to do is modify the where condition. Let me show you an example of Deleting All SQL Agents Jobs except the replication ones. The below query shows example of jobs related to transaction replication and the default one “syspolicy_purge_history".

Script To Delete All SQL Agent Jobs Except Few:

DECLARE @SQLStatement VARCHAR(200)
DECLARE @jobName SYSNAME
   DECLARE c1 CURSOR FOR
   SELECT name FROM msdb.dbo.sysjobs_view WHERE name not in
('syspolicy_purge_history' 
,'Agent history clean up: distribution' 
,'Distribution clean up: distribution' 
,'Expired subscription clean up' 
,'INDSBLRHT0VR72-AdventureWorks2017-2' --Change the job name as per your environment
,'INDSBLRHT0VR72-AdventureWorks2017-Adven_Pub-2' --Change the job name as per your environment
,'INDSBLRHT0VR72-AdventureWorks2017-Adven_Pub-INDSBLRHT0VR72-5' --Change the job name as per your environment
,'Reinitialize subscriptions having data validation failures' 
,'Replication agents checkup' 
,'Replication monitoring refresher for distribution' 
)

   OPEN c1
   FETCH NEXT FROM c1 INTO @jobName
     IF @@CURSOR_ROWS=0
     PRINT 'No Job found! Please re-check LIKE operator.'
     WHILE @@fetch_status=0
    BEGIN
       SET @SQLStatement= 'EXEC msdb.dbo.sp_delete_job @job_name =''' + @jobName + ''', @delete_unused_schedule=1' 
       PRINT(@SQLStatement)
     --EXEC (@SQLStatement)   --Uncomment to Execute
       FETCH NEXT FROM c1 INTO @jobName
    END
   CLOSE c1
   DEALLOCATE c1

Another way of achieving the same end result is using the first query and filter out the jobs from the result set.
Hope this helps. Feel free to leave your comments.
You also might find the below scripts useful:

Query To Retrieve All Permissions for a user in Selective databases
Script to Rename SQL Agent Jobs
Script to get SQL Cluster Failover Time and Node Name
Script to find the SQL Agent Job Name in Execution
List all permissions for a user in all or selective databases