SQL Maintenance Cleanup Task Working but Not Deleting

Question

I have a Maintenance Plan that is suppose to go through the BACKUP folder and remove all .bak older than 5 days. When I run the job, it gives me a success message but older .bak files are still present.

I've tried the step at the following question: https://serverfault.com/questions/245493/sql-maintenance-cleanup-task-success-but-not-deleting-files

Result is column IsDamaged = 0

I've verified with the following question and this is not my issue: https://serverfault.com/questions/94094/maintenance-cleanup-tasks-running-successfully-but-not-deleting-back-up-files

I've also tried deleting the Job and Maintenance Plan and recreating, but to no avail.

Any ideas?

Answer 1

Try these checks:

1. Use *.* for the file extension or bak without a dot, both of which I have found work if other issues are correct too.
2. Make sure that the path is simply the path to where your backups are but with a backslash on the end.
3. Check that verify is ticked when you create the back up in the first place.

Answer 2

This is often caused by permission problems. The cleanup task doesn't seem to log anything helpful when permissions prevent the account under which the step is running from deleting files.

You can verify this as follows:

• In SQL Server Management Studio, right-click on your maintenance plan and select "Modify"
• Locate the Maintenance Cleanup Task used to delete your bak files and click on the "View T-SQL" button. Copy the script to your clipboard - it will be something like "EXECUTE master.dbo.xp_delete_file ..."
• Connect to the server using a Windows account that has the required permissions on the folder containing the backups and run the SQL
• If the bak files do get cleaned up, then this indicates that the Maintenance Plan task is configured correctly and that you have a permissions problem.
• In Management Studio, open the properties window for the Job (SQL Server Agent > Jobs), click the Edit button on the first step. The "Run as" section will indicate which account is running the job.

Answer 3

Had same issue. Culprit is the .Bak extension. Change it to Bak and you should be good.

Answer 4

Make sure you create the Maintenance Plans in the right SQL Server. To be more detail,

If you have SQL Server 2005 and you create maint. plans under this SQL Server 2005, you will ONLY be able to "clean up" (delete) those backup (bak) and transaction log (trn) generated / backed-up from a SQL Server 2005 Server. If you tried to clean up those bak or trn from 2008, 2008 R2, 2012 or newer, it won't work. (Due to the file header info). That is, 2005 doesn't recognize those files in 2008 or newer format !

However, you can always clean up those files by creating maint. plans under SQL Server 2008 and "clean up" those files from 2005 ~ 2012 (tested).

Which means, 1. 2005 can only clean up bak/trn in 2005 format 2. 2008 can clean up 2005 ~ 2012 format

I didn't have chance to test 2000 (too old) or 2014 (too new). But I guess 2014 should work from 2008.

Answer 5

I'll put me 2 cents in, just been over this issue as well, have new deployment with SQL 2012. Backup jobs working correctly, however clean up tasks for both logs and old backups didn't do a thing although were completed successfully.

The problem in my opinion amongst those silly things, I've set extension as .bak and .txt, however as soon as I changed them to .BAK and .TXT (in capitals) it started to work.

Hope it helps someone who's troubleshooting similar issue.

Answer 6

I've had similar issues with jobs before. The cases I ran into with it not deleting were because a location was not explicitly set when I went through the GUI. Even if I didn't change anything, when the path location was not specifically listed, it was like it didn't know where to look to process the delete so no deletes ever occurred. It backed up fine and everything was good, but it wouldn't cleanup as specified in the wizard/form.

Answer 7

I have had the same problem and I tried to solve it as well. I think I tried every combination but it did not work. Note that the xp_delete_file is undocumented and obviously very buggy.

But what I did and can assist you is to change the step to a PowerShell step.

You can use the following to delete files that are older than 30 days

get-childitem c:\sqlbackup -recurse | where {$.lastwritetime -lt (get-date).adddays(-30) -and -not $.psiscontainer} |% {remove-item $_.fullname -force -whatif}

Note the -whatif that is added so you can test.

But in my case that was not enough. There was a rights issue with the PowerShell approach. The account that is running the SQL Agent did not have rights to delete the files. When setting the rights correctly everything worked like a charm.

Good luck

Answer 8

I found that I had to change my frequency to days from 1 week and then it would delete the old backups. Why, I don't know, but that did resolve the issue.