Enable and Disable SQL Server Agent Jobs for Maintenance Mode

By: Kun Lee | Comments (1) | Related: > SQL Server Agent

Problem

Sometimes when we conduct SQL Server Maintenance we need to shutdown the SQL Server Service and the SQL Server Agent once the maintenance is complete. In these circumstances, I typically manage (shut down and start) the SQL Server service first then start the SQL Server Agent later. However, I have several jobs that run every minute to every one hour and I do not want those jobs to start with SQL Server Agent. I want to selectively test some SQL Server Agent Jobs without starting all of the Jobs. Do you have any suggestions on how to accomplish this? Check out this tip for the solution.

Solution

There are several ways to manage disabling and enabling the SQL Server Agent Jobs during a maintenance window. Here are a few options:

One way is to create a staging table with the Job name and the status. Before performing any maintenance, update the staging table. Based on the data in the staging table you can manage the jobs. This works well, but if someone runs the Job to update the staging table by accidently during the maintenance window the table may have all disabled statuses. On the other side of the coin, I have also seen situations where the table is not updated before the maintenance runs and we have just as bad of a problem because we are not confident the data is correct.
A second option is to build a script to disable and re-enable the SQL Server Agent Jobs before the maintenance window occurs. This too can be an issue if the DBA forgets to generate the script, but it does serve as a static record of the job status when the maintenance process began.
A third option is to change the start date of the job to be after the maintenance window is scheduled to be complete. This may be feasible on some SQL Servers and not on others, but with this being said, I typically do not disable the job, but change the start date to be some reasonable time in the future. I can change the start date either manually in Management Studio or by a T-SQL script.

Check out the scripts and screen shots below before your next maintenance window.

T-SQL script to generate a script to disable and enable SQL Server Agent jobs

The script below has two sections. First, is to disable the Jobs that are currently enabled. This is a script you would typically use at the beginning of the maintenance window. The second script is to enable the jobs that are currently enabled. This would be used at the end of the maintenance window to ensure the correct jobs are enabled. Like I mentioned above, you can change the script below to save the current status to a table, but I like capturing the scripts.

-- If there is a single quote, please make sure to replace
USE msdb
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @JobName VARCHAR(500)
DECLARE @SQLCMD VARCHAR(1000)
PRINT '-- Disable Job'
DECLARE job_cursor CURSOR FAST_FORWARD FOR 
SELECT name FROM sysjobs
WHERE enabled = 1
OPEN job_cursor
FETCH NEXT FROM job_cursor INTO @JobName
WHILE @@FETCH_STATUS = 0
BEGIN
-- EXEC sp_update_job @job_name = @JobName, @enabled = 0
SET @SQLCMD = 'EXEC sp_update_job @job_name = ' 
   + '''' + @JobName + '''' 
   + ', @enabled = 0' + CHAR(10) + 'GO'
PRINT @SQLCMD
FETCH NEXT FROM job_cursor INTO @JobName
END
CLOSE job_cursor
DEALLOCATE job_cursor
PRINT '-- Enable Job'
DECLARE job_cursor CURSOR FAST_FORWARD FOR 
SELECT name FROM sysjobs
WHERE enabled = 1
OPEN job_cursor
FETCH NEXT FROM job_cursor INTO @JobName
WHILE @@FETCH_STATUS = 0
BEGIN
-- EXEC sp_update_job @job_name = @JobName, @enabled = 1
SET @SQLCMD = 'EXEC sp_update_job @job_name = ' 
  + '''' + @JobName + '''' + ', @enabled = 1' 
  + CHAR(10) + 'GO'
PRINT @SQLCMD
FETCH NEXT FROM job_cursor INTO @JobName
END
CLOSE job_cursor
DEALLOCATE job_cursor

Changing Start Date for a job manually

Another option is to use SQL Server Management Studio to change the start date for the job. Here is screenshot highlighting where the parameter would be changed. You will need to change the schedule for "Start Date" to be something reasonable. I typically change it to something like the next day.

Changing Start Date for that job by scripting

As an alternative to using SQL Server Management Studio to manage the SQL Server Agent Jobs, you can also script out the code to set the start date to the future. In the three sections below, we provide code for SQL Server 2000, 2005 and 2008.

SQL Server 2000 Script

USE msdb
DECLARE @jobname varchar(128)
DECLARE @schedulename varchar(128)
DECLARE @startdate INT
DECLARE @MaintDays TINYINT
/*##############################################*/
-- This is the variable to set how many days to move 
SET @MaintDays = 0
/*##############################################*/
SET @startdate = CAST(REPLACE(CONVERT(CHAR(10),GETDATE()+@MaintDays,20),'-','') AS INT)
PRINT @startdate
DECLARE schedule_cursor CURSOR FOR 
SELECT sj.name as jobname, sjs.name as jobschedulename 
FROM sysjobs sj join sysjobschedules sjs on sj.job_id = sjs.job_id
WHERE sj.enabled = 1 and sjs.enabled = 1 
and sjs.active_end_date > CAST(REPLACE(CONVERT(CHAR(10),GETDATE()+2,20),'-','') AS INT)
OPEN schedule_cursor
FETCH NEXT FROM schedule_cursor INTO @jobname, @schedulename
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_update_jobschedule 
@job_name= @jobname, 
@name = @schedulename, 
@active_start_date=@startdate
PRINT @jobname
FETCH NEXT FROM schedule_cursor INTO @jobname, @schedulename
END
CLOSE schedule_cursor
DEALLOCATE schedule_cursor

SQL Server 2005 Script

use msdb
DECLARE @jobname varchar(128)
DECLARE @schedulename varchar(128)
DECLARE @startdate INT
DECLARE @MaintDays TINYINT
/*##############################################*/
-- This is the variable to set how many days to move 
SET @MaintDays = 2
/*##############################################*/
SET @startdate = CAST(REPLACE(CONVERT(CHAR(10),GETDATE()+@MaintDays,20),'-','') AS INT)
PRINT @startdate
DECLARE schedule_cursor CURSOR FOR 
select sj.name, ss.name 
from sysjobs sj 
join sysjobschedules sjs on sj.job_id = sjs.job_id
join sysschedules ss on sjs.schedule_id = ss.schedule_id
where sj.enabled = 1 and ss.enabled = 1
and ss.active_end_date > CAST(REPLACE(CONVERT(CHAR(10),GETDATE()+2,20),'-','') AS INT)
OPEN schedule_cursor
FETCH NEXT FROM schedule_cursor INTO @jobname, @schedulename
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_update_jobschedule 
@job_name= @jobname, 
@name = @schedulename, 
@active_start_date=@startdate
PRINT @jobname
FETCH NEXT FROM schedule_cursor INTO @jobname, @schedulename
END
CLOSE schedule_cursor
DEALLOCATE schedule_cursor

SQL Server 2008 Script

use msdb
DECLARE @schedule_id INT
DECLARE @startdate INT
DECLARE @MaintDays TINYINT
/*##############################################*/
-- This is the variable to set how many days to move 
SET @MaintDays = 2
/*##############################################*/
SET @startdate = CAST(REPLACE(CONVERT(CHAR(10),GETDATE()+@MaintDays,20),'-','') AS INT)
PRINT @startdate
DECLARE schedule_cursor CURSOR FOR 
SELECT schedule_id 
FROM sysschedules 
WHERE enabled = 1 and active_end_date > @startdate -- sqL 2008
OPEN schedule_cursor
FETCH NEXT FROM schedule_cursor INTO @schedule_id
WHILE @@FETCH_STATUS = 0
BEGIN
-- EXEC msdb.dbo.sp_update_schedule @schedule_id= 345 , @active_start_date=20080808
EXEC msdb.dbo.sp_update_schedule 
@schedule_id= @schedule_id , 
@active_start_date=@startdate 
PRINT @schedule_id
FETCH NEXT FROM schedule_cursor INTO @schedule_id
END
CLOSE schedule_cursor
DEALLOCATE schedule_cursor

Next Steps

Managing SQL Server Jobs before, during and after a maintenance window is very critical. Your best bet is to have a plan to manage the jobs to not cause more work for yourself or alert other groups during off hours with a false positive situation.
Review your current database maintenance procedures to see if adding these scripts or processes to your plan would help.
Check out these related tips:

About the author

Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips