Maintenance Plan not deleteing old backup files

Question

Maintenance Plan not deleteing old backup files

BigSam

SSCrazy

Points: 2146
More actions
June 19, 2010 at 9:13 am

#222397

I have Maintenance Plans for groups of my databases: System, User & Management databases. Each of these have backup jobs that have the 'Create a sub-directory for each database' enabled. The backups are working as expected, but the delete old backup files doesn't delete anything, nor does it indicate why not.
In the delete step I set the folder to the same as the backup & enabled the 'Include first-level subfolders' parameter. The files are deleted based on the age of the file.
I have SQL running in a domain with a domain account (user). I gave this account full permission in the folder where the backups reside. The account is not a member of the local group allowing remote login (for debugging purposes I could, but since I don't see any events in the logs showing a problem or failure I haven't done this).
I've viewed the T-SQL, copied it & executed it, but again no files are deleted. I ran the command while logged in as a domain administrator.
I've seen some other complaints regarding this @ Microsoft, but still haven't seen a solution. Has anyone else encountered this problem & found a work-around or solution.
Thanks,
BigSam

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

Did you put .bak or just bak for the extension? If the former, this is your problem. You can open the maintenance plan, open the backup task and view the SQL. Do that and post the results here so we can see exactly what is being executed.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

BigSam SSCrazy Points: 2146 More actions · Answer 2

BigSam

SSCrazy

Points: 2146

June 21, 2010 at 6:14 am

#1182677

I've tried *.bak & .bak

Same situation.

george sibbald SSC Guru Points: 104210 More actions · Answer 3

have you tried just bak (no preceding dot). This is what it should be set to.

---------------------------------------------------------------------

franco SSCommitted Points: 1642 More actions · Answer 4

For what I have understood you have to automate a SSIS Package by using SQL Server Agent in order to perform the Maintenance Cleanup Tasks.

You can find additional information and a video that explain all that stuff at the following link:

http://msdn.microsoft.com/en-us/library/dd440761.aspx

Franco

David Sorauer SSCrazy Points: 2874 More actions · Answer 5

I have the same problem with one of our SQL 2005 servers.

As a work around, I run:

declare @dt datetime

select @dt=getdate()-1

EXECUTE master.dbo.xp_delete_file 0,N'BackupLocation',N'BAK',@dt,1

this has worked perfectly for me.

David Sorauer SSCrazy Points: 2874 More actions · Answer 6

btw, I just have this scheduled each night as SQL Agent job.

Sankar Reddy SSCrazy Points: 2410 More actions · Answer 7

BigSam (6/19/2010)
I have Maintenance Plans for groups of my databases: System, User & Management databases. Each of these have backup jobs that have the 'Create a sub-directory for each database' enabled. The backups are working as expected, but the delete old backup files doesn't delete anything, nor does it indicate why not.
In the delete step I set the folder to the same as the backup & enabled the 'Include first-level subfolders' parameter. The files are deleted based on the age of the file.
I have SQL running in a domain with a domain account (user). I gave this account full permission in the folder where the backups reside. The account is not a member of the local group allowing remote login (for debugging purposes I could, but since I don't see any events in the logs showing a problem or failure I haven't done this).
I've viewed the T-SQL, copied it & executed it, but again no files are deleted. I ran the command while logged in as a domain administrator.
I've seen some other complaints regarding this @ Microsoft, but still haven't seen a solution. Has anyone else encountered this problem & found a work-around or solution.
Thanks,
BigSam

[font="Courier New"]This is a bug in SQL Server 2008.

https://connect.microsoft.com/SQLServer/feedback/details/496419/xp-delete-file-silently-skips-files-if-there-are-ntfs-permission-issues

http://sqlblog.com/blogs/andy_leonard/archive/2009/03/11/xp-delete-file.aspx

Normally I won't recommend using maintenance plans and even encourage folks to move away from these maintenance plans and go for T-SQL scripts. There are tonnes of T-SQL scriprs out there that get the job done in a much more efficient and elegant way than the MP. One script I highly recommend using are from Ola Hallengren. I am using these scripts in several of my servers and work great and have high quality code and are flexible than the maintenance plans.

http://ola.hallengren.com/[/url]

[/font]

[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]