Ramblings of a DBA

Tara Kizer
posts - 166, comments - 837, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

Disable all jobs on a SQL Server

Have you ever needed to quickly disable all jobs on a SQL Server?  If you have a lot of jobs to disable, then this stored procedure will help you out.  We move our systems to our disaster recovery site twice per year for disaster recovery testing.  As part of this process, we need to disable all jobs on our SQL Servers.  It doesn't take a whole lot of time to do this inside Enterprise Manager, but when your goal is to complete your work quickly so that the customer impact is minimal, you want to save all of the seconds that you can.

CREATE PROC isp_Disable Jobs

AS

SET NOCOUNT ON

CREATE TABLE #Job_Names

 Job_Name SYSNAME NOT NULL
)

INSERT INTO #Job_Names
SELECT name
FROM msdb.dbo.sysjobs
ORDER BY name

DECLARE @job_name SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER

DECLARE disable_jobs CURSOR FOR 
SELECT Job_Name
FROM #Job_Names

SET @job_id = NULL

OPEN disable_jobs
FETCH NEXT FROM disable_jobs INTO @job_name

WHILE @@FETCH_STATUS = 0
BEGIN


 EXEC msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT

 EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0
 
 SET @job_id = NULL

 FETCH NEXT FROM disable_jobs INTO @job_name

END

CLOSE disable_jobs
DEALLOCATE disable_jobs

DROP TABLE #Job_Names

RETURN

 

If you want to quickly enable all jobs, just change @enabled = 0 to @enabled = 1.  Change the stored proc name as well so that it makes sense.

Print | posted on Friday, October 17, 2003 1:41 PM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# re: Disable all jobs on a SQL Server

Hi I'm a 8th
4/13/2004 4:17 PM | Jennifer Oliver
Gravatar

# re: Disable all jobs on a SQL Server

Thank you for posting this! The code was much useful.
7/20/2004 9:57 AM | Steve
Gravatar

# re: Disable all jobs on a SQL Server

Try this it does not require a temp table, and should use less memory because of that.


CREATE PROC isp_Disable Jobs

AS
SET NOCOUNT ON


DECLARE @job_name SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER

DECLARE disable_jobs CURSOR FOR
SELECT Cast(name as SYSNAME)
FROM msdb.dbo.sysjobs
ORDER BY name

SET @job_id = NULL

OPEN disable_jobs
FETCH NEXT FROM disable_jobs INTO @job_name

WHILE @@FETCH_STATUS = 0
BEGIN


EXEC msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT

EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0

SET @job_id = NULL

FETCH NEXT FROM disable_jobs INTO @job_name

END

CLOSE disable_jobs
DEALLOCATE disable_jobs

RETURN


GO
10/28/2005 8:27 AM | Fred
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET