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?

score 69 · Answer 1 · edited Jan 14 '16 at 21:07

69

Try these checks:

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

edited Jan 14 '16 at 21:07

Grant

11,799
13
42
47

answered Jul 25 '11 at 07:40

sturb

699
5
3

21

using extension bak is what worked for me. No dots and asterisks – jao Mar 26 '13 at 13:02
2

We had this issue -- changed the path to end in "\" and also changed the file spec to "*.*". That worked. – Matt Dawdy Dec 22 '14 at 21:02
for me when I changed daily to weekly it started working. Anyone knows the solution to that? – Ahsan Apr 07 '17 at 09:44

score 10 · Answer 2 · answered Mar 25 '14 at 17:08

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.

In my case it was in fact a permission issue. Thanks for helping me to figure it out! — Bryan Bedard, Apr 30 '18 at 02:48

score 7 · Answer 3 · answered Jun 25 '13 at 15:49

7

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

answered Jun 25 '13 at 15:49

jordan koskei

2,749
1
15
12

3

Someone at Microsoft needs to be lynched. Their standard is that filenames are case insensitive but case preserving - I spent 6 hours chasing this. – pojo-guy Jul 29 '16 at 00:24
You're a champion. My issue was with the extension as well. .bak doesn't work, however without the fullstop, bak by itself functions like a kung fu master teaching his newest class. LIKE A CHAMPION. Kudos to you – Gawie Schneider Sep 21 '20 at 10:10

score 2 · Answer 4 · answered Jul 17 '14 at 16:43

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.

Thanks. Ran across [this blog post](https://blog.netnerds.net/2012/03/when-xp_delete_files-doesnt-work-as-expected/) that seems to confirm that `xp_delete_file` *does* attempt to read the file header before deleting, and therefore will skip files made by later versions of SQL or by 3rd-party utilities. (A practical corollary is that `xp_delete_file` is probably vastly slower than other methods like PowerShell that look only at the path/extension.) — BradC, Dec 04 '18 at 15:24
Well, it's good xp_delete_file does the job. Cause people may have other *.bak file but is totally nothing to do with SQL server (could be other software file happen to be the same file extension). By reading the file header, it deletes only the SQL Server/recognized file. — Chjquest, Dec 05 '18 at 16:07

score 2 · Answer 5 · edited Jul 28 '14 at 09:35

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.

score 1 · Answer 6 · answered Jul 19 '13 at 08:33

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

score 1 · Answer 7 · answered Jul 09 '14 at 15:57

1

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.

answered Jul 09 '14 at 15:57

Cindy

11
1

exactly same for me, did you resolve this issue? I want it for work for days – Ahsan Apr 07 '17 at 09:37

score 1 · Answer 8 · answered Mar 16 '11 at 15:20

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.

answered Mar 16 '11 at 15:20

Thyamine

1,228
7
10

Exactly, I successfully .bak files but can't CLEANUP the same .bak files. I tried to DELETE SPECIFIC FILE instead of files in a folder and received this error: **Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'D:\\Program F..." failed with the following error: "Error executing xp_delete_file extended stored procedure: Specified file is not a SQL Server backup file.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.** How do I go about verifying the path as you mentioned? – Alex Mar 16 '11 at 15:34
Under the Cleanup Tasks make sure everything is listed properly. The full folder name and the file extension. In the past I've seen it allow blank fields (assuming the defaults) and had it not run/cleanup properly. – Thyamine Mar 16 '11 at 19:19

score 0 · Answer 9 · answered Jan 08 '14 at 18:08

0

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.

answered Jan 08 '14 at 18:08

PseudoToad

1,504
1
16
34

score 0 · Answer 10 · edited Feb 04 '14 at 18:36

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.

score 0 · Answer 11 · answered Feb 21 '23 at 20:50

Check if the backup files do not have the read-only option checked, if it is checked then the xp_delete_file procedure will not delete them(it will execute and return no error). I had one such case where the database backup application set the read-only attribute on the backup files made.

SQL Maintenance Cleanup Task Working but Not Deleting

11 Answers11

Linked