Ola Hallengren solution, backups not cleaning up

Question

I have about 30 SQL Server 2005-2014 instances, all of which use the excellent Ola Hallengran maintenance script to produce my full/diff/log backups. This has worked for me for years with no issues, until recently.

One of the servers has two instances. In the case of the default instance on that server, the cleanup of the .bak files does not occur.

I wouldn't assume this has anything to do with the script, but rather something else like perhaps the .bak files are locked when the cleanup occurs. I have changed the scheduling of the backups in case the backup window is clashing with another process, to no avail. None of the other instances have this issue, including the named instance on the same box.

I know for a fact this is not a permissions issue, as the instance's Agent runs under a domain account with full control of the backup target volume, which is a dedicated share on a physical server, RAID 5 (unfortunately). It has group policy permissions as follows:

• bypass traverse checking
• log on as batch job

I have checked all the event logs on the backup server and see nothing that would help me troubleshoot it.

Can anyone offer any advice as to how I might be able to troubleshoot this issue?

Thanks

Update:

• Date/Time on Server is consistent with the DC and my workstation. The backup server also has perfect time sync so definitely not a time slippage issue.
• The @cleanupTime is just as I'd left it (72 hours).
• I had initially assumed tape backups of that volume might be the culprit, but have since checked and triple checked that the files are not being locked - our Veeam backup of that volume only happens once a week.

I can delete the files manually with no issues.

On your advice I have added -o to the end of the command to get visibility on how the commands look. The only thing I think might be potentially the cause is:

Date and time: 2015-10-20 11:29:58 Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N'\backupServer\Production\SQLInstance01\Reporting_Database\FULL', 'bak', '2015-10-17T03:29:58' IF @ReturnCode <> 0 RAISERROR('Error deleting files.', 16, 1) Outcome: Succeeded Duration: 00:00:00

So the command appears to be using a UNC path which is not terminated with a backslash. I have read elsewhere that the xp_delete_file SP requires a backslash:

https://stackoverflow.com/questions/24582996/sql-server-xp-delete-file-parameters

So this is probably a red herring but based on the -o output, the xp_delete_file SP wouldn't work correctly. I'm going to check the output on another instance which doesn't exhibit this problem to compare. Thanks for your suggestions.

Update#2: I have added the -o to my command on another instance which does correctly cleanup files and found that the output for this also does not have a trailing \ in the output file, so I'm still at a loss.

Update#3: I tried deleting the filed by dropping the command from the output file into SSMS. What I have found is that the command works correctly on any other instance, just not this one. **It says "Command Completed Successfully" but no files are deleted. Is there an instance-level setting that would enable/disable the ability to use xp_delete_file?

Answer 1

This is a list of random things that come to mind, I can't guarantee any are the problem.

• DateTime on Server has been knocked out
• DateTime on the Backup store has been knocked out
• Someone has changed the @CleanupTime on your agent job (potentially accidentally)
• There is a backup of your backup location occurring while the cleanup is trying to run locking the files (have had this one myself)

Since you've said that this is a recent problem not a just happened I'm presuming you can still delete the files manually which would indicate they are not in permanent use.

It may be worth running the -o command at the end to write the output of the command to a file ( where it is '-b' chance to '-b -o C:\Logs\backuplog.txt') for example and consult that for if it is bringing back any problems

Answer 2

I just had this happen to me. Out of 50 instances, old backups for one instance were not being deleted. The job made a new backup and did not fail when deleting old backups. I did not notice until another server backup job failed because of a lack of space. (Yes, not very proactive.) Anyway, this was the only instance where the SQL engine service account did not have modify rights. Once those were added, all was well. I'm wondering if the job should not fail if it fails to delete - perhaps it's an option in the Ola scripts. I came across this because I was looking to see if others addressed this. Thought I'd add my 2 bits.

Answer 3

I know this is an old post, but if you are searching this issue here's another possibility. We just started using DFS replication. There's a filter to exclude tmp and bak extensions by default. Depending upon which DFS server was active, the backups were either there or not there. After the filter was adjusted, new files got replicated. However, the old files did not replicate and reside only on the DFS server share where they were first created.

One SQL server was handling new backups and deleting most old backups just fine, but the job running Ola's script would not delete the really old ones from before the replication was fixed. This is because it did not see these files on the DFS share it was using at the time.

My desktop was set to use the other DFS share which had the original old files (not replicated) plus the new files (replicated), so I could see them.

This was really perplexing at first when I was looking for a backup. I just stared at the screen a while at files that had to be there but were not.