Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Enable and Disable SQL Server Agent Jobs for Maintenance Mode


By:   |   Last Updated: 2010-06-15   |   Comments (1)   |   Related Tips: More > 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.

Another option is to use SQL Server Management Studio to change the start date for the job

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()[email protected],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, 
@[email protected]
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()[email protected],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, 
@[email protected]
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()[email protected],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 , 
@[email protected] 
PRINT @schedule_id
FETCH NEXT FROM schedule_cursor INTO @schedule_id
END
CLOSE schedule_cursor
DEALLOCATE schedule_cursor
Next Steps


Last Updated: 2010-06-15


next webcast button


next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, June 17, 2010 - 8:01:10 AM - ALZDBA Back To Top

- you need to take into account jobs can be launched using sqlagent alerts !!
If you disable a job, the alert will try to launch it - which will fail because the job is disabled - and it will keep on trying to launch the job x-times per second !! These failures are recorded in sqlagent.out untill the disk is full and the system blocks !!

- also keep in mind any disabled job can still  be launched using SSMS or sp_start_job !!
Altough it is a bad practise from managebility point of view, many applications use sp_start_job to perform asynchrone operations.

 - In stead of just disabling the job, we also leave a visible trail by prefixing a disabled job by "who/why".
This way, anybody can see why the job is disabled and sp_start_job requests will fail ( as should - IMHO)

 

Off course, when re-enabling these jobs, you also need to re-enable the corresponding alerts.


Learn more about SQL Server tools