Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Special Announcement: SQL Server Performance Tuning Tips and Tricks Webinar
 

Disable or Enable SQL Server Agent Jobs using PowerShell


By:   |   Read Comments (3)   |   Related Tips: More > 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


Last Update:


next webcast button


next tip button



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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

Now working for Remote named instances

 


Tuesday, March 06, 2018 - 6:35:47 PM - bass_player Back To Top

 

Praveen,

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


Tuesday, March 06, 2018 - 2:25:53 AM - K Praveen Kumar Back To Top

 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

 


Learn more about SQL Server tools