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
