Modifying SQL Server Agent Settings Using PowerShell


By:   |   Updated: 2015-04-22   |   Comments   |   Related: More > PowerShell


Problem

In our quest to standardize SQL Server installs, one of the things we need to alter are the SQL Server Agent settings for Maximum History Rows (MaximumHistoryRows) and Maximum Job History Rows (MaximumJobHistoryRows). These settings determine how much job history is stored overall and the maximum for one job. These changes can be done using SQL Server Management Studio (SSMS) or using T-SQL, but in this tip we will show how this can be done using PowerShell.

Solution

In this solution I'm using the PowerShell module SQLPS as provided with SQL Server 2014. As you may or may not know, SQL Server Management Objects ( SMO / SQLSMO ) are exposed when you use SQLPS, which allows us to do what we want to do. One thing to note is that not everything related to SQL Server Agent can be done via PowerShell at this time.

SMO - SQLAgent

Did you ever look at the SQL Server Management Objects Object model? As they say, a picture is worth a thousand words, so have a quick peek at the SMO Object Model Diagram.

When browsing the SMO object model at the server level, you'll encounter a tree called JobServer. In that branch you'll find everything SQL Agent related.

Since one of the big advantages of PowerShell is that you get to work with objects, instead of text, browsing the SMO object model is fairly simple and straight forward as shown below.

<#
.SYNOPSIS
show SQLAgent properties
.DESCRIPTION
show SQLAgent properties

.NOTES
-Date 2015-03-24 - Author Bijnens Johan

#>

# Check module SQLPS
if ( !(get-module -name SQLPs ) ) {

# save original location
Push-Location
# SQLPs will set the current location to SQLSERVER:\ !!
# -DisableNameChecking -> avoid remarks about non-discoverable function names
import-module -name SQLPs -DisableNameChecking | out-null

#reset current location to original location
Pop-Location

}

#Interrupt when errors occur
Trap {
# Handle the error
$err = $_.Exception
write-host $err.Message
while( $err.InnerException ) {
$err = $err.InnerException
write-host $err.Message
};
# End the script.
break
}

# Using Windows authenticated connection

Clear-host
$db = get-sqldatabase -serverinstance .\sql2014DE -name msdb
# Select SQLAgent
$db.parent.JobServer ;

#Close connection
$db.Parent.ConnectionContext.Disconnect();


on my laptop the result is:

SQLAgent properties result

To figure out which properties can be modified via PowerShell, you can use the following command:

<#
.SYNOPSIS
show SQLAgent properties that can be modified
#>
# Select SQLAgent
$db.parent.JobServer.Properties | where writable -eq $true | out-gridview ;

SQLAgent properties writable result

You can see why I like the out-gridview instead of a long text list.

Without going into the details of all these SQL Agent properties, the ones we are focusing on for now MaximumHistoryRows and MaximumJobHistoryRows.

According to MSDN:

  • - MaximumHistoryRows : An Int32 value that specifies the maximum number of history rows retained.
  • - MaximumJobHistoryRows : An Int32 value that specifies the maximum number of history rows retained per job.

To modify these properties, simply provide a new integer value and execute the JobServer.Alter() method.

You need to keep in mind the MaximumJobHistoryRows cannot be a higher value than the MaximumHistoryRows. SMO has implemented this check and will throw an error if you try to do otherwise. You can see below where I got an error when I tried this.

SQLAgent properties errormessage

PowerShell Script to Change SQL Agent MaximumHistoryRows and MaximumJobHistoryRows

Below you'll find the full script to modify the MaximumHistoryRows and MaximumJobHistoryRows values.
For simplicity, I'm using a variable named $SQLAgent instead of addressing the properties directly using long dotted-references.

<#
.SYNOPSIS
Modify SQLAgent MaximumHistoryRows and MaximumJobHistoryRows values

.DESCRIPTION
Modify SQLAgent MaximumHistoryRows and MaximumJobHistoryRows values
replacement for:
USE [msdb]
GO
-- enable Agent XPs
EXEC sp_configure 'Agent XPs','1'
RECONFIGURE
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=100000, @jobhistory_max_rows_per_job=1000
go


.NOTES
-Date 2015-03-24 - Author Bijnens Johan

#>

#To be provided !
[string]$Servername="YourLogicalSQLServerName" ;
[string]$InstanceName = 'YourInstanceName';


# Check module SQLPS
if ( !(get-module -name SQLPs ) ) {

# save original location
Push-Location
# SQLPs will set the current location to SQLSERVER:\ !!
# -DisableNameChecking -> avoid remarks abount non-discouverable function names
import-module -name SQLPs -DisableNameChecking | out-null

#reset current location to original location
Pop-Location

}

#Interrupt when errors occur
Trap {
# Handle the error
$err = $_.Exception
write-error $err.Message
while( $err.InnerException ) {
$err = $err.InnerException
write-error $err.Message
};
# End the script.
break
}

Clear-host
$TargetMaximumHistoryRows = 100000;
$TargetMaximumJobHistoryRows = 1000 ;

try {
# Using Windows authenticated connection
$db = get-sqldatabase -serverinstance "$Servername\$InstanceName" -name msdb
# Select SQLAgent
$SQLAgent = $db.parent.JobServer ;
# Show settings
$CurrentSettings = $SQLAgent | select @{n="SQLInstance";e={$db.parent.Name}},MaximumHistoryRows, MaximumJobHistoryRows ;
$CurrentSettings | ft -AutoSize ;

if ( $CurrentSettings.MaximumHistoryRows -ne $TargetMaximumHistoryRows -or $CurrentSettings.MaximumJobHistoryRows -ne $TargetMaximumJobHistoryRows ){
Write-Warning 'Altering SQLAgent settings';
$SQLAgent.MaximumHistoryRows = $TargetMaximumHistoryRows ;
$SQLAgent.MaximumJobHistoryRows = $TargetMaximumJobHistoryRows ;
$SQLAgent.Alter();
# ensuring we have the latest information
$SQLAgent.Refresh();
$SQLAgent | select @{n="SQLInstance";e={$db.parent.Name}},MaximumHistoryRows, MaximumJobHistoryRows ;
}
#Close connection
$db.Parent.ConnectionContext.Disconnect();
}
catch{
# Handle the error
$err = $_.Exception
write-error $err.Message
while( $err.InnerException ) {
$err = $err.InnerException
write-error $err.Message
};
# End the script.
break
}

#write-host 'The end' ;
Write-Output 'The end' ;

In my test case, these were my results:

Final result

Now you may be wondering why I commented the "write-host 'The end' ;" statement and added the "write-output 'The end' ;". As I was testing my script I noticed the following:

aha erlebnis

Only after modifying the commented write-host statement to write-output, the "The end" text appeared where I would expect it to appear. I haven't yet explained for myself why this PowerShell behavior is like this, but I'm sure someone will point me to the correct references pretty soon. :-)

Conclusion

It didn't take much time to discover SQL Server's job system browsing the SMO object model using PowerShell. As shown, it is fairly easy to modify SQL Agent properties.

I hope you've enjoyed this little alternative to manage these SQL Server Agent settings.

Next Steps


Last Updated: 2015-04-22


get scripts

next tip button



About the author
MSSQLTips author Johan Bijnens Johan Bijnens is a database firefighter, working with SQL Server since 2000, working at Aperam Genk.

View all my tips
Related Resources





Comments For This Article





download





Recommended Reading

Setting the PowerShell Execution Policy

How to find a specific text string in a SQL Server Stored Procedure, Function, View or Trigger

Using PowerShell to Work with Directories and Files

Bulk Copy Data from Oracle to SQL Server

Execute SQL Server Stored Procedures from PowerShell








get free sql tips
agree to terms


Learn more about SQL Server tools