Availability Group - Deleting old backup files I'm using Azure VMs with a SQL Server Availability Group, and Ola Hallengren' scripts. Backups are working without issues, however I've noticed that old backup files are not being removed from the server. 

To remove the files, I'm running this command;

DECLARE @BackupDir NVARCHAR(4000) = (SELECT BackupDir = dbo.fn_SQLServerBackupDir())
DECLARE @Date DATETIME = DATEADD(DAY, -4, GETDATE())

EXECUTE [master].[dbo].xp_delete_file 0, @BackupDir, 'bak', @Date, 1;


Note: The function gets the default backup location for the SQL Instance.

Whenever I run this command, it completes successfully, but no files are removed. The Cleanup process that is included in the Ola Hallengren script is also unable to remove files.

In other environments, that are not AGs, the above command, plus the Cleanup in Ola Hallengren' script, work without issues.

This leads me to think the issue is either environment related or has something to do with the fact the backups where performed on databases in an AG.

Has anyone else encountered something like this before and might be able to offer some insight?

Backups are stored on the F:\ drive, which is a local disk on the server. I have also confirmed that the correct permissions have been set. At the moment, I've set full control for Everyone. (Environment is not live yet).

I tried modifying the function to add the \ at the end of the backup path, but this did not fix the issue. I've confirmed that there are backups which meet the date requirement, too.

EDIT 1:
Here is the backup command that's running, which includes the @Cleanup parameter, that doesn't remove the files;

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@BackupType = 'Log',
@Verify = 'Y',
@Checksum = 'Y',
@Compress = 'Y',
@LogToTable = 'Y',
@CleanupTime = 0,
@AvailabilityGroupDirectoryStructure = '{DatabaseName}',
@AvailabilityGroupFileName = '{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}_{Month}_{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256'
, @ServerCertificate = 'Cert_Name'
Since you mentioned this is in an AG, are you sure you're running the command and checking the backup folder on the same server?

If you are connecting to the Availability Group primary through the AG listener, then running the xp_delete_file command will run on whichever physical server happens to be the primary replica at that time.

Try running SELECT @@SERVERNAME to verify which server you're connected to when you run the backup deletion command, then check that server's F:\ drive to see if backups were deleted or not.  

You should also verify that you are getting a 0 as the return code from the extended stored procedure:

DECLARE @ReturnCode int;
EXECUTE @ReturnCode = [master].[dbo].xp_delete_file 0, @BackupDir, 'bak', @Date, 1;
IF @ReturnCode &lt;&gt; 0 PRINT @ReturnCode;


Note that the example call to xp_delete_file you provided is targeting files with a .bak extension.  The example call to Ola's backup script is targeting log backups, which will have an extension of .trn by default.  This may be contributing to the discrepancy you're seeing.



By the way, I would strongly recommend that you take backups to somewhere other than the local drive.  Not only does it represent a single point of failure for your SQL Server instance and critical database backups, but it can actually be pretty confusing with an AG when doing restores.

In particular, if your transaction log backups are being taken to two different locations (local folders on each replica), restoring to a point in time over a period that includes an AG failover will be tough.  You'll have to get the log files from both servers and make sure they get restored in the right order.  The process is much simpler if they all go to a network location like \\servername\backups\MyAgListenerName\DatabaseName\LOG.