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
@cleanupTimeis 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?
Best Answer
This is a list of random things that come to mind, I can't guarantee any are the problem.
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