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.[@more@]

ITPub博客

Disable all jobs on a SQL Server

最新文章