By: Kun Lee | Updated: 2010-06-15 | 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
Article Last Updated: 2010-06-15
