Disable or Enable SQL Server Agent Jobs using PowerShell

By:   |   Comments (5)   |   Related: > PowerShell


Problem

In a previous tip on Disabling or Enabling SQL Server Agent Jobs, we have seen how we can disable or enable SQL Server Agent Jobs using T-SQL. However, in order to properly disable the SQL Server Agent jobs, we need to use the sp_update_job stored procedure and iterate thru all of the jobs using cursors, setting the enabled property to false. Otherwise, a restart of the SQL Server Agent service would be required. Can this be done using Windows PowerShell?

Solution

Enabling and disabling SQL Server Agent jobs are part of my high availability and disaster recovery implementations, particularly used with log shipping and database mirroring where I have copies of the jobs on both the production and the standby SQL Server instances. In the past I have used the sp_update_job system stored procedure to enable and/or disable the jobs, iterating thru them using cursors. I run the T-SQL script below on the appropriate SQL Server instance to either enable or disable the jobs.

SET NOCOUNT OFF

DECLARE
@jobID UNIQUEIDENTIFIER   --variable for job_id
DECLARE planidCursor CURSOR FOR  --used for cursor allocation
 
SELECT job_id FROM msdb.dbo.sysjobs WHERE enabled=1
OPEN planidCursor
FETCH NEXT FROM planidCursor INTO @jobID
WHILE @@Fetch_Status = 0
   
BEGIN
           
--disable SQL Server Agent Job - @enabled parameter value is 0, enabled parameter value is 1
           
EXEC msdb.dbo.sp_update_job @job_id=@jobID, @enabled = 0
         
         
FETCH Next FROM planidCursor INTO @jobID
   
END
CLOSE
planidCursor
DEALLOCATE planidCursor

As you have seen in previous PowerShell tips, this task can be done easily and can even be executed against multiple SQL Server instances. Let's start by loading the appropriate .NET assemblies used by SMO as defined in this previous tip.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

Next, we'll create an instance of the Server class to represent the SQL Server instance that you will be connecting to, passing the SQL Server instance name. I will be assigning the results to a variable named $serverInstance, so we can access the different properties and methods of the Server class thru this variable.

$serverInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "InstanceName"

Referencing another previous tip, we can use the Server.JobServer class to represent the SQL Server Agent associated with an instance of SQL Server. This includes the SQL Server jobs, operators and alerts. The JobServer class has the Jobs property that represents a collection of all of the SQL Server Agent jobs. We can iterate thru all of the enabled SQL Server Agent Jobs as follows:

foreach ($jobs in ($serverInstance.JobServer.Jobs | Where-Object {$_.IsEnabled -eq $TRUE}))
{
 $jobs
}

The property that we are concerned about in this tip is the IsEnabled property. However, you can explore the different properties, methods and events of the class by piping it to the Get-Member cmdlet.

$serverInstance.JobServer.Jobs | Get-Member

Disable or Enable SQL Server Agent Jobs Using PowerShell

To disable or enable the SQL Server Agent Jobs, we need to set the IsEnabled property to either true or false.

 $jobs.IsEnabled = $FALSE #Disables the SQL Server Agent Job

It's not enough to set the IsEnabled property to either true or false. Anytime a property is changed, the Alter() method needs to be called to persist the change.

 $jobs.Alter() #Persist the change made to the property

The complete script to enable or disable the SQL Server Agent jobs is shown below.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST"
#Create an instance of the Jobs object collection from the JobServer property
foreach ($jobs in ($serverInstance.JobServer.Jobs | Where-Object {$_.IsEnabled -eq $TRUE}))
{
 $jobs.IsEnabled = $FALSE
 $jobs.Alter()
} 

This can be translated into a function that you can use to iterate thru all of the SQL Server instances listed in a text file and disables all of the SQL Server Agent jobs on all of those instances. I bet that this is a lot easier and scalable than using the T-SQL script, especially when you need to run this across multiple SQL Server instances.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
function Disable-SQLAgentJobs
{
    $serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $args
    #Create an instance of the Jobs object collection from the JobServer property
    foreach ($jobs in ($serverInstance.JobServer.Jobs | Where-Object {$_.IsEnabled -eq $TRUE}))
    {
     $jobs.IsEnabled = $FALSE
         $jobs.Alter()
    }
}
ForEach($s in Get-Content servers.txt) {Disable-SQLAgentJobs $s} 

NOTE: Remember to change the boolean values from FALSE to TRUE and vice versa to reverse the logic of the script.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

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

View all my tips



Comments For This Article




Friday, October 22, 2021 - 11:45:31 AM - bass_player Back To Top (89351)
Just replace the parameter @enabled = 1 as per the code comment

Friday, October 22, 2021 - 8:44:36 AM - DJango Back To Top (89348)
Many thanks for your time writing this up - it was extremely useful :)

How would you do this but for enabling the job schedule too please?

Wednesday, May 23, 2018 - 5:18:34 AM - Angel Back To Top (76004)

Now working for Remote named instances

 


Tuesday, March 6, 2018 - 6:35:47 PM - bass_player Back To Top (75362)

 

Praveen,

Simply replace LOCALHOST with the name of your SQL Server instance.


Tuesday, March 6, 2018 - 2:25:53 AM - K Praveen Kumar Back To Top (75355)

 Hi Sir,

The above Script of powershell script of disabling the sql job is working on my local host but when i connect to my remote sql server instances it doesnot disable the sql job which i have specified. Is there anything i should alter in the Powershell Script, if you give a tip it will be more useful and helpful to me.I am basically want to use it for my deployment purposes.

 

Thanks

Praveen

 















get free sql tips
agree to terms