Advertisements
Home > DBA Stuff, SQL Tips > SQL DBA – Disable/Enable multiple SQL Jobs at once

SQL DBA – Disable/Enable multiple SQL Jobs at once


Seems to be a simple topic. But yes when it comes to do these type of tasks we tend to recall the syntax and end up searching internet (bing/google) for the solution.

–> Disabling a single SQL Job can be done simply through SSMS. Right click on the SQL Job and select Disable. To enable it back simply select Enable for a disabled Job.

This can also be done by a TSQL query as shown below:

USE msdb;
GO

-- Disable a SQL Job:
EXEC dbo.sp_update_job
    @job_name = N'syspolicy_purge_history',
    @enabled = 0 ;
GO

select enabled, * from sysjobs where name = 'syspolicy_purge_history'
GO

–> Now if you’ve to Disable Multiple or All the Jobs in SQL Agent, how will you do it?

Selecting All SQL Jobs on SSMS and right clicking won’t give you the Disable option. And here it become more tricky as there is only way to do this by TSQL query. But there is no single TSQL query defined in SQL Server to Disable all SQL Jobs at once. So, we will have to create a Dynamic SQL which will create Script for all SQL Jobs dynamically to Disable each and every SQL Job. Let’s see how:

USE msdb;
GO

-- Disable Multiple SQL Jobs at once:
DECLARE @dynSql NVARCHAR(MAX) = ''

SELECT @dynSql += N'exec msdb.dbo.sp_update_job @job_name = ''' + name + N''', @enabled = 0;' + CHAR(10) + CHAR(13)
FROM msdb.dbo.sysjobs
WHERE enabled = 1
ORDER BY name;

PRINT @dynSql;
-- Here is the output of above PRINT statement:
exec msdb.dbo.sp_update_job @job_name = 'ExecuteSPuspGetBillOfMaterials', @enabled = 0;

exec msdb.dbo.sp_update_job @job_name = 'syspolicy_purge_history', @enabled = 0;

Simple Copy-Paste the the above Dynamically generated SQL Script and Execute it, it will Disable all SQL Jobs at once.

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

%d bloggers like this: