0

Consider a scenario where you have an AG running across 3 nodes and your backups are being backed up locally. Each node have all the default jobs deployed and scheduled to run at same time. As the databases are part of the AG, backups are only being taken on the PRIMARY NODE. After failover to a different node, the backups stored in the previous PRIMARY are not being deleted.

I thought that this could be happening since the default for the cleanup is AFTER_BACKUP and since no backups were happening on that node, the cleanup was not happening. I tried changing this to 'BEFORE_BACKUP' to no avail.

Am I missing anything obvious or is there anything I can do ?

Command running on every node


     sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBAAdmin -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'L:\Backups', @BackupType = 'FULL',@Compress='Y', @Verify = 'Y', @CleanupTime = 72, @CheckSum = 'Y', @LogToTable = 'Y'" -b

Thank you

  • 2
    You're storing backups of the primary on the primary? – Aaron Bertrand May 16 '18 at 14:01
  • Aaron makes a great point and the easiest solution is to get a shared directory away from all three servers for the backups to be pointed to. For your issue at hand, what does the command log show? Are delete statements being created? Does the SQL job maintain and clean the folder if it has not failed over? Does the checklist on this FAQ help? ola.hallengren.com/frequently-asked-questions.html – Shaulinator May 16 '18 at 14:04
  • @AaronBertrand that is correct but we have a robocopy to copy them into a NAS location. Unfortunately writing directly to a network share proved to cause a big slow down and was decided to backup directly to disk and then later having an async copy to the NAS. – MrKudz May 16 '18 at 14:06
  • @Shaulinator I checked the commandLog audit table and it's only deleting files for the files that are not part of the AG ( the ones that go within the folder with the node name). Cleanup is successful if it's the primary. – MrKudz May 16 '18 at 14:12
  • 1
    Do you have the latest version of Ola? I would verify you are on the latest version and test it once more with the before option just in case it has been fixed. – Shaulinator May 16 '18 at 14:23
2

The current design is that the stored procedure will decide if the database should be backed up. Only if the database should be backed up, it will go into the code that does the work (creates sub-directories, backup, verify, and cleanup).

It could make sense that if you are running with @CleanupMode = 'BEFORE_BACKUP', then it should delete backups, even if the database should not be backed up.

Could you create an issue for this on GitHub.

https://github.com/olahallengren/sql-server-maintenance-solution/issues

  • Will do Ola. Thank you for taking the time to answer my question. – MrKudz May 16 '18 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.