Disable or Enable All SQL Server Agent Jobs

By: Alejandro Cobar | Updated: 2021-08-17 | Comments (1) | Related: > SQL Server Agent

Problem

When you have a High Availability/Disaster Recovery setup using Availability Groups, Database Mirroring, or Log Shipping, you know that the management of SQL Server Agent Jobs requires some effort to keep things working properly. Sometimes this effort involves things like: manually disabling jobs on one server and enabling them on the other server or writing code that checks if the current instance is the Primary and have those jobs enabled and the Secondary server jobs disabled.

In any case, having control of this aspect is very important as there might be critical jobs that must be running constantly to perform specific business logic that would represent an issue if left disabled. With that said, I’m presenting a solution that can help you achieve enabling and disabling jobs with little effort.

*You might already have a solution in place that works for you, and that’s fantastic; however, I’m just throwing out an additional option to add to our DBA toolbox.

Solution

Within this module, I will present a PowerShell script that based on the parameters passed, it will either enable or disable the intended SQL Server Agent job.

Initial Considerations

Make sure that you have connectivity between the server where you will be executing the PowerShell script, and the server where the target SQL Server instance resides.
Make sure that the account that will be connecting to the target SQL Server instance has enough privileges to connect to the msdb system database and execute sp_update_job.

PowerShell Script

Parameters

The script has 3 mandatory parameters that must be provided by the user in order to accomplish its purpose:

$instance: the name of the target SQL Server instance to connect to
$job: the name of the SQL Agent job whose status is intended to be modified
$action: 0 if the job will be disabled or 1 if the job will be enabled

Script execution

From a PowerShell window, execute the script as follows. Make sure to specify the path where you have placed the script. In this example, I’m targeting my default SQL Server instance and will disable the job called “test”.

PS C:\temp> .\Get-MSSQL-Instance-Jobs-Flip.ps1 localhost test 0

Before attempting to do anything, the script will prompt you with the credentials to connect to the specified SQL Server instance, as follows. Something to consider, the output of the credentials prompt can vary on the way you are executing the script. For instance, if you fire the script from PowerShell ISE, then it will look like the following.

But if you open PowerShell, using the non-ISE environment, it will look like this:

Regardless of the environment you select, even if it’s a bit obvious, here’s what each one means:

Trusted = the connection to the SQL Server instance will be made with the same user being used for the execution of the PowerShell script (basically, you don’t have to specify any credentials, it will assume them based on the context).
Windows Login = A Windows login has to be provided for the correct authentication.
SQL Login = A SQL login has to be provided for the correct authentication.

If the option you select has issues to connect to the SQL Server instance, then the script will notify you:

What happens if you don’t specify the mandatory parameters, and attempt to run the script?

The script will ask you for each, one by one, which is not quite useful if you use this script as a solution to address any particular automation use case.

Here’s the output of the script and a screenshot of the status of the test job, within my SQL Server instance, after running the script:

Let’s run it again, but with the $action parameter set to 1:

Now let’s attempt to connect to a SQL Server instance that doesn’t exist (or it isn’t reachable through the network). The script will let you know if it is unable to establish the connection.

Now let’s attempt to connect to a correct SQL Server instance, but let’s target an agent job that doesn’t exist. The script will let you know that such agent job doesn’t exist.

Addressing multiple jobs at once within a SQL Server instance

Here’s one way to easily target multiple jobs. You can create a file that invokes the PowerShell script, addressing one job per line, with its intended status. For instance, if I have 3 jobs called test, test2 and test3 and want to disable all of them, I’d run the following in a command prompt window. The only code tweak that would be required is to somehow pass the login credentials to the script (or have them already baked in) so that they are not prompted and your input isn’t required every time the script is triggered.

powershell C:\temp\Get-MSSQL-Instance-Jobs-Flip.ps1 localhost test 0
powershell C:\temp\Get-MSSQL-Instance-Jobs-Flip.ps1 localhost test2 0
powershell C:\temp\Get-MSSQL-Instance-Jobs-Flip.ps1 localhost test3 0

Download Scripts

Use this link to download the PowerShell script from this article

Next Steps

You can incorporate this script in your environment, after proper testing of course, to have a way to manage agent jobs within the SQL Server instances that you have deployed.

You can leverage the power of this script to automate flipping of any job(s) that your use case requires. Perhaps using a central server to orchestrate the job(s) flipping from there. In the end, whatever makes your life easier will always be a good thing.

Check out more PowerShell tools that I have contributed to the community:

About the author

Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-08-17

Thursday, September 9, 2021 - 9:41:20 AM - alzdba	Back To Top (89217)
Always nice to have such a tool, however you need to be able to track down the actions you have performed, so you only enable the jobs you did disable at this point in time ! In the TSQL-script I use, I prefix the jobs name with 'DBA_yyyymmdd_' for every job that is being disabled. Doing that makes it easy to select and enable only the jobs in that scope.