Sql server: disable all sql jobs at once using TSQL script – Agent Jobs

The article explain tips for SQL Server disable all sql jobs topic. All of as sometimes in our work need to do automatic jobs. One of them can disable all Jobs on SQL Server Agent. Having for example 10 jobs is very easy to disable one by one. How about 50 jobs or maybe 100 Jobs? We will need some automatic script to this jobs quickly for as. We are going to explain how to disable all sql jobs at once, using specific name or using specific category.

Why will need to disable or Enable all jobs?

You perform some task every day on midnight and need to be disabled all jobs on Agent.

Planning some database system restore.

To perform some task every month on specific day and need to be disabled all jobs.

Planning to Perform SQL Database migration.

 

Sql Server Disable all Sql Jobs

  • Find all enabled or disabled jobs on SQL Server
  • Enable or Disable all sql jobs using table updates
  • Disable or Enable all jobs using stored procedures
    • Disable using specific name of jobs
    • Using stored procedures to Disable all jobs
  • Disable jobs by Job Category

Find all enabled or disabled jobs on SQL Server

First use the following script to find Enabled jobs:


--Find enabled jobs

USE msdb

GO

SELECT name,enabled

FROM dbo.sysjobs

Disable or Enable all jobs using Table Updates

—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

The procedures above use update directly to msdb table and is not recommended from Microsoft.

Read also other SQL Server Helping Posts:

  1. Reset SQL Server Password CMD
  2. Find Location of SQL Server Error Log
  3. Fix SQL server error 26 and error 40

Disable or Enable all jobs using stored procedures

Enable or disable job using stored procedures and specific name of job.

READ ALSO -   How to Read Data from LDF file in SQL Server – A Complete Guide

Disable or Enable jobs using specific name of job


--Disable Jobs By Job Name
USE msdb ;
GO

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


--Enable Jobs By Job Name
USE msdb ;
GO

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

Disable all sql jobs using stored procedures

This is the right procedure recommended by Microsoft.

First create commands using the following script:


-- For disable all Jobs

SELECT 'EXEC msdb.dbo.sp_update_job @job_name = ''' + name + N''', @enabled = 0;'

FROM dbo.sysjobs


-- For enable all Jobs

SELECT 'EXEC msdb.dbo.sp_update_job @job_name = ''' + name + N''', @enabled = 1;'

FROM dbo.sysjobs

"<yoastmark

The commands above will generate the scripts to disable or enable all jobs on your SQL Server using stored procedure. Will generate 1 row for every job on your Agent Jobs. Copy the scripts and execute.

The scripts will be like:


EXEC msdb.dbo.sp_update_job @job_name = 'SSIS Server Maintenance Job', @enabled = 1;

EXEC msdb.dbo.sp_update_job @job_name = 'syspolicy_purge_history', @enabled = 1;

Disable jobs by Job Category

Sometimes we need to disable only jobs on specific category. To execute this task we use the following Script:


USE MSDB;
GO
UPDATE J
SET J.Enabled = 0
FROM MSDB.dbo.sysjobs J
INNER JOIN MSDB.dbo.syscategories C
ON J.category_id = C.category_id
WHERE C.[Name] = 'Database Maintenance';
GO

If you have any question about SQL Server disable all sql jobs feel free to ask on the comment section.

Please rate use if this article was helpful to you!

Related Articles