SQL Server Blog

I discovered a small issue in production today. Our new DR server which is currently going through the rigors of testing had all of the Scheduled Jobs left enabled. I was confused why I was getting two alerts for some data validation processing we perform overnight with two different sets of data.

After a few minutes thought, I realised it could only be the DR system and I wanted a quick way to disable all of the jobs rather than shut down the Agent service which would trigger an alert in our monitoring software.

Here is my simple query to disable all jobs as well as one to re-enable, although our DR process is a little more sophisticated to only online jobs which are enabled in production.

/* Disable All Jobs */
DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = ”
SELECT @SQL = @SQL + N’EXEC msdb.dbo.sp_update_job @job_id = ”’ + CAST(job_id AS VARCHAR(36)) + ”’, @enabled = 0;’
FROM msdb.dbo.sysjobs

EXEC (@SQL)

/* Enable All Jobs */
DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = ”
SELECT @SQL = @SQL + N’EXEC msdb.dbo.sp_update_job @job_id = ”’ + CAST(job_id AS VARCHAR(36)) + ”’, @enabled = 1;’
FROM msdb.dbo.sysjobs

EXEC (@SQL)