SQL with Manoj

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.