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 JobsASSET NOCOUNT ONCREATE TABLE #Job_Names
(
Job_Name SYSNAME NOT NULL
)INSERT INTO #Job_Names
SELECT name
FROM msdb.dbo.sysjobs
ORDER BY nameDECLARE @job_name SYSNAME
DECLARE @job_id UNIQUEIDENTIFIERDECLARE disable_jobs CURSOR FOR
SELECT Job_Name
FROM #Job_NamesSET @job_id = NULLOPEN disable_jobs
FETCH NEXT FROM disable_jobs INTO @job_nameWHILE @@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
ENDCLOSE disable_jobs
DEALLOCATE disable_jobsDROP TABLE #Job_NamesRETURN
