SQL Agent – Disable All Jobs (script)

Another addition to your cookbook, comes in very handy during releases!

The scripts below are easier, but if you want to avoid updating system tables directly…please scroll down.

————————————————–

Disable All SQL Server Agent Jobs
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO

Enable All SQL Server Agent Jobs
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 1
WHERE Enabled = 0;
GO

Disable Jobs By Job Name
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] LIKE 'Admin%';
GO

Enable Jobs By Job Name
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 1
WHERE [Name] LIKE 'Admin%';
GO

————————————————–

Disable Jobs By Job Name
USE msdb ;
GO

EXEC dbo.sp_update_job
@job_name = N'SomeJob',
@enabled = 0;
GO

Enable Jobs By Job Name
USE msdb ;
GO

EXEC dbo.sp_update_job
@job_name = N'SomeJob',
@enabled = 1;
GO

Disable All ENABLED SQL Server Agent Jobs

SET NOCOUNT ON

DECLARE @Job_Names TABLE
(
Job_Name SYSNAME NOT NULL
)

INSERT INTO @Job_Names
SELECT s.name
FROM msdb.dbo.sysjobs s
WHERE s.Enabled = 1 --Optional filter
ORDER BY s.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

Enable All DISABLED SQL Server Agent Jobs

SET NOCOUNT ON

DECLARE @Job_Names TABLE
(
Job_Name SYSNAME NOT NULL
)

INSERT INTO @Job_Names
SELECT s.name
FROM msdb.dbo.sysjobs s
WHERE s.Enabled = 0 --Optional filter
ORDER BY s.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 = 1
SET @job_id = NULL
FETCH NEXT FROM disable_jobs INTO @job_name

END

CLOSE disable_jobs
DEALLOCATE disable_jobs

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s