By: Johan Bijnens | Comments | Related: > 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:
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 ;
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.
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:
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:
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
- Test the script, inspect its results
- Explore the SQL Server Management Objects library
- Take a look at other PowerShell scripts on MSSQLTips - https://www.mssqltips.com/search.asp?q=powershell%20
- Enjoy the marvels of PowerShell and SQLPS
About the author
Johan Bijnens is a database firefighter, working with SQL Server since 2000, working at Aperam Genk.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
