Tuesday, April 7, 2009

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.

No comments: