SQL Server: Quickly clean backup history with dbatools

I just had to restore a database in production for my customer. Before doing the restore I have the habit to query the msdb.dbo.backupset table to get an overview of the last backups.

When running my query, I felt it was taking longer than usual. So out of curiosity, I looked at the SSMS standard report “Disk Usage by Top Tables”. Here is the output.

This instance contains dozens of databases in Always On Availability Groups with a transaction log backup frequency set to 30 minutes. The backup history has never been cleaned, which explain the large number of rows.

It’s not often that I see the msdb database with a size of 3.5GB, so I decided it’s time to delete the backup history. My customer got many instances that are configured and managed the same way so I’m sure this phenomenon will be present on many servers.

I could easily use the system stored procedure sp_delete_backuphistory but I instead decided to use PowerShell and dbatools. I just recently started to use dbatools and I want to practice more using PowerShell for tasks like this one that needs to be done on many instances.

First, like the SSMS report I’d like to get the row count and the amount of data used by the backup history tables in all my MSDB databases. I want to measure the actual gain in data space after the cleaning. To do this, I decided to use the Get-DbaDbTable function from dbatools.

Get-DbaDbTable -SqlInstance 'InstanceName' -Database msdb `
    | Where-Object {$_.Name -Like 'backup*'} `
    | Select-Object -Property Name, RowCount, DataSpaceUsed `
    | Out-GridView

I use a Central Management Server as an inventory for my SQL servers.

The list of servers can be easily retrieved from the CMS with Get-DbaRegisteredServer.

$Servers = Get-DbaRegisteredServer -SqlInstance 'MyCmsInstance' | Where-Object {$_.Group -Like '*Prod*'};

I have 36 production servers.

PS C:\> $Servers.Count
36

Now, looping through each instance I do the sum of the backup history rows with the total space used.

$Servers = Get-DbaRegisteredServer -SqlInstance 'MyCmsInstance' | Where-Object {$_.Group -Like '*Prod*'};
foreach ($srv in $Servers) {
    Get-DbaDbTable -SqlInstance $srv -Database msdb `
        | Where-Object {$_.Name -Like 'backup*'} `
        | ForEach-Object -Process {$rowsBefore+=$_.RowCount; $sizeBefore+=$_.DataSpaceUsed}
}
Write-Output "backup history total rows: $rowsBefore" 
Write-Output "backup history total size: $sizeBefore" 

PS C:\>
backup history total rows: 31989560
backup history total size: 10343088

I’ve got a total of almost 32 Million rows of backup history on my production servers for a total data size exceeding 10 GB.

To clean the backup history, I use the Remove-DbaDbBackupRestoreHistory function. I decide to keep a backup history of about 4 months, so I choose the arbitrary number of 120 as value for the KeepDays parameter.

foreach ($srv in $Servers) {
    Remove-DbaDbBackupRestoreHistory -SqlInstance $srv -KeepDays 120 -Confirm:$false
}

After cleaning the backup history I run once again the first loop to get the msdb tables information so I can compare the row count and data space used before and after the Remove function.
Here is the result.

Diff rows: 24654309
Diff size: 8047072

I just deleted over 24 Million rows, about 8GB of data space in the msdb databases over 36 instances. All this was done with a few lines of PowerShell and dbatools in a really short time. As a DBA managing dozens of instances, automating and scripting tasks like this with dbatools becomes very easy and can save a lot of time.

You can find below the whole script, please feel free to comment if you think it can be written in a more efficient way. I will take any advice on PowerShell scripting.

$Servers = Get-DbaRegisteredServer -SqlInstance 'MyCmsInstance' | Where-Object {$_.Group -Like '*Prod*'};
foreach ($srv in $Servers) {
    Get-DbaDbTable -SqlInstance $srv -Database msdb `
        | Where-Object {$_.Name -Like 'backup*'} `
        | ForEach-Object -Process {$rowsBefore+=$_.RowCount; $sizeBefore+=$_.DataSpaceUsed}
}
Write-Output "backup history total rows: $rowsBefore" 
Write-Output "backup history total size: $sizeBefore" 

foreach ($srv in $Servers) {
    Remove-DbaDbBackupRestoreHistory -SqlInstance $srv -KeepDays 120 -Confirm:$false
}

Start-Sleep -Seconds 10

foreach ($srv in $Servers) {
    Get-DbaDbTable -SqlInstance $srv -Database msdb `
        | Where-Object {$_.Name -Like 'backup*'} `
        | ForEach-Object -Process {$rowsAfter+=$_.RowCount; $sizeAfter+=$_.DataSpaceUsed}
}
$diffRows= $rowsBefore-$rowsAfter
$diffSize= $sizeBefore-$sizeAfter

Write-Output "Diff rows: $diffRows" 
Write-Output "Diff size: $diffSize"