SQL Maintenance Cleanup Task Working but Not Delet

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

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 2

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 3

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 4

To throw my 2 cents in...mine was failing when I tried to delete maintenance files. Although I had the extension and file location set correctly, I had forgotten to set it from Backup Files to Maintenance Plan Files.

Answer 5

Issue drove me crazy. I have a work around, though other servers use the maintenance plan w/o issue. I copied the T-SQL script and made a sp changing the dbo to sys. It works for me. Script for reads

Create Procedure bk_removeTLogBackupFiles
as
Declare @DeleteDate varchar(50)
Declare @DeleteExecuteSQL varchar(1000)
Set @DeleteDate = cast(DATEADD(day,-7,GetDate()) as varchar(50))
Set   @DeleteExecuteSQL =
'EXECUTE master.sys.xp_delete_file 0,N''\\Backupserver\BackupFolder\' + @@servername + '\User'',N''trn'',N' + quotename(@DeleteDate,'''') +  ',1'


Execute (@DeleteExecuteSQL)

This is a generic script I use for all my backups going to a certain server with security in the server\folder leverl sorted into folders for user system etc. Not much, but it worked for me.

Answer 6

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).affffdays(-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