Background
My colleague and I were recently looking for ways to improve our production Availabiity Group nodes. These are beefy machines with a large amount of memory, and things like buffer cache scans can be lengthy. In his research, my teammate came across discussions on best practices for the Target Recovery Time setting of databases. You can read more about it here, but the TLDR is that it’s recommended to set at 60. Awesome! Now, how do we do that on hundreds of databases? Enter one of my best friends, PowerShell.
A couple of caveats to note:
- You must have the module dbatools installed.
- If the instance is in an Availability Group, you can only run this on the primary. You’ll have to fail over in order to update the databases on the secondary (or wait until a maintenance window when you’re already failing them over).
I hope this helps! If you have any notes about how this can be improved, or even a different method altogether that would be better, I’d love to hear it. Enjoy!
Script Details
In order to address this issue at scale I wrote the following PowerShell script. I’ll go through section by section, but if you’d like to skip ahead the full script is at the bottom.
Parameters
This section lets us pass in a parameter for the SQL Server instance name. It might seem like a small thing, but this adds flexibility so that we can run it remotely and also reuse the script easily on multiple instances.
(Usage: .\TargetRecoveryTime.ps1 HOSTNAME\INSTANCENAME)
# Parameter passed in for target SQL Instance.
Param(
[Parameter(Mandatory=$True,Position=1)]
[string]$sqlInstance
)
Location and Modules
This section allows us to import the necessary modules, namely dbatools. Before doing that, it saves our current directory so that afterward we can return to the same location.
# Save current working directory.
Push-Location
# Import necessary modules
Import-Module dbatools
# Return to working directory.
Pop-Location
Export Current Settings
While not entirely necessary in this case, I find that it’s always a good idea to record what the original values were before you change them. You never know when you might need to revert or reference historical data later. To that end, this section exports a text file containing the current Target Recovery Time settings for every database on the instance. It uses the instance name in the file naming so that you can run the script against multiple instances without overwriting your work.
# Set name of export file. Avoids file path errors by removing the \ from the instance name.
$settingsList = $sqlInstance.Replace('\','_') + "_TRTSettings.txt"
# Export a list of the current database settings for later reference.
Invoke-DbaQuery -SqlInstance $sqlInstance -Query "SELECT name,target_recovery_time_in_seconds FROM sys.databases;" | Out-File $settingsList
Setting the Target Recovery Time Values
And finally, we get to the magic. In this final section, we collect a list of the databases on the instance and iterate through it with a foreach loop, setting our desired value for TARGET_RECOVERY_TIME on each one.
# Get a list of all databases on the instance
$databaseList = Get-DbaDatabase -SqlInstance $sqlInstance
# Loop through the list of databases and change the TRT setting.
foreach ($database in $databaseList)
{
Invoke-DbaQuery -SqlInstance $sqlInstance -Query "ALTER DATABASE $database SET TARGET_RECOVERY_TIME = 60 SECONDS WITH NO_WAIT;"
}
Full Script (SetTargetRecoveryTime.ps1)
###########################################################################################################
# Script Purpose: Sets the TARGET_RECOVERY_TIME on all databases in an instance to 60 seconds.
# Refrence: https://www.sqlservercentral.com/articles/target-recovery-interval-and-indirect-checkpoint
# Author: Landon Fowler
# Created: 11/28/2023
###########################################################################################################
# Parameter passed in for target SQL Instance.
Param(
[Parameter(Mandatory=$True,Position=1)]
[string]$sqlInstance
)
# Save current working directory.
Push-Location
# Import necessary modules
Import-Module dbatools
# Return to working directory.
Pop-Location
# Set name of export file. Avoids file path errors by removing the \ from the instance name.
$settingsList = $sqlInstance.Replace('\','_') + "_TRTSettings.txt"
# Export a list of the current database settings for later reference.
Invoke-DbaQuery -SqlInstance $sqlInstance -Query "SELECT name,target_recovery_time_in_seconds FROM sys.databases;" | Out-File $settingsList
# Get a list of all databases on the instance
$databaseList = Get-DbaDatabase -SqlInstance $sqlInstance
# Loop through the list of databases and change the TRT setting.
foreach ($database in $databaseList)
{
Invoke-DbaQuery -SqlInstance $sqlInstance -Query "ALTER DATABASE $database SET TARGET_RECOVERY_TIME = 60 SECONDS WITH NO_WAIT;"
}
Leave a Reply