olahallengren
Deleting Encrypted Backup Files
So this isn't an issue that is likely fixable, but one that probably needs listing on the FAQs. If you encrypt your backup files to disk then xp_delete_file can't read the headers and therefore doesn't delete the files. However it "fails" silently, which means there's no obvious issue until the disk space alerts / runs out.
A possible workaround would be an additional SQL Agent job that runs a PowerShell script (working on that in my environment now).
SQL Server version and edition Microsoft SQL Server 2019 (RTM-CU16-GDR) (KB5014353) - 15.0.4236.7 (X64) May 29 2022 15:55:47 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)
Version of the script --// Version: 2022-01-02 13:58:13
This is my PowerShell workaround based on the Ola folder structures. Tested and working as a SQL Agent Job using a PowerShell type.
$FullBackupRetention = -672 $DiffBackupRetention = -336 $LogBackupRetention = -168
$folders = Get-ChildItem -Path B:\SQLBackups -Recurse -Directory -Filter "FULL"## | Select FullName%{$_.FullName}
foreach ($folder in $folders) { ##$folder.FullName Get-ChildItem -Path $folder.FullName -File -Filter "*.bak" | Where-Object{$_.LastWriteTime -lt (Get-Date).AddHours($FullBackupRetention)} | Remove-Item -Verbose
}
$folders = Get-ChildItem -Path B:\SQLBackups -Recurse -Directory -Filter "DIFF"## | Select FullName%{$_.FullName}
foreach ($folder in $folders) { ##$folder.FullName Get-ChildItem -Path $folder.FullName -File -Filter "*.bak" | Where-Object{$_.LastWriteTime -lt (Get-Date).AddHours($DiffBackupRetention)} | Remove-Item -Verbose
}
$folders = Get-ChildItem -Path B:\SQLBackups -Recurse -Directory -Filter "LOG"## | Select FullName%{$_.FullName}
foreach ($folder in $folders) { ##$folder.FullName Get-ChildItem -Path $folder.FullName -File -Filter "*.trn" | Where-Object{$_.LastWriteTime -lt (Get-Date).AddHours($LogBackupRetention)} | Remove-Item -Verbose
}