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
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
- Review the previous tip on Checking SQL Server Agent jobs using Windows PowerShell and all PowerShell-related tips.
