4

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?

  • "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." Can you explain what you mean by the command not working correctly? You get an error, or just no file is deleted, or something else? There is no instance-level setting I'm aware of that would silently break xp_delete_file. – James L Oct 20 '15 at 12:16
  • Have you tried running the xp_delete_file by hand in the SSMS as the SQL agent account to see if the execution of the command is throwing an error that is not being caught? – Aaron Oct 20 '15 at 14:06
3

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

  • Ola's scripts have checks to ensure transaction log backups are not deleted if there are no, more recent full backups. With that in mind, I'd check that good backups have been taken and verified more recently than your cleanup point, and that a good DBCC CHECKDB has happened. The scripts may be skipping the deletes where it can't verify that more recent good backups exist. – Matthew Swain Mar 15 '17 at 11:32

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.