SQL 2005 sp2; Old Backups will not delete in Maintenance Cleanup Task

Question

SQL 2005 sp2; Old Backups will not delete in Maintenance Cleanup Task

reggieep

Grasshopper

Points: 17
More actions
August 20, 2008 at 4:49 pm

#197214

Please come up with something; as we are, installing this on our clients Product Servers

Viewing 14 posts - 1 through 13 (of 13 total)

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

Irish Hall of Fame Points: 3958 More actions · Answer 1

Please come up with something; as we are, installing this on our clients Product Servers

Not quite enough to go on here. In 2005 maintenance plans, you have to create a separate step to remove old backups. It is called a Maintenance Cleanup task.

Regards, Irish

KDASQL Ten Centuries Points: 1103 More actions · Answer 2

And make sure you specific the interval you want the clean up task to execute on....daily, weekly and also provide the correct extension of the file that the clean task is supposed to get rid of.

jrmtl Ten Centuries Points: 1075 More actions · Answer 3

Same problem here and the old files are not being deleted ?

I apologize as I have created a duplicate thread

Irish Hall of Fame Points: 3958 More actions · Answer 4

Same problem here and the old files are not being deleted ?

Are you saying that you have added a Cleanup Task and are still having issues?

I've noticed that there are a number of folks that upgraded from 2000 to 2005 and are surprised that their old maintenance plans don't work the same way. I don't know if this is your case, but thought I would mention that.

In any case, you need to look at the design and add the task to the maintenance plan, either before the database backup runs or after it succeeds. I recommend deleting the files after the backup completes if space allows.

Regards, Irish

jrmtl Ten Centuries Points: 1075 More actions · Answer 5

This is a new sql 2005 install and I have indeed added the task to the maintenance plan and created that link but old files specified are still there as if it's not running

Irish Hall of Fame Points: 3958 More actions · Answer 6

Right click on that part of the task and select the Edit option. In this screen you can select the location and file extension as well as how old the files need to be.

I viewed the T-SQL and my setup looks like this:

EXECUTE master.dbo.xp_delete_file 0,N'e:\server\backup',N'bak',N'2008-08-27T10:50:50'

In my case, I have seperate jobs for both Full and Transaction Log backups, each with their own Maintenance Cleanup task since the file extensions are different.

Regards, Irish

jrmtl Ten Centuries Points: 1075 More actions · Answer 7

jrmtl

Ten Centuries

Points: 1075

August 29, 2008 at 11:10 am

#865163

That's what I have also !

jrmtl Ten Centuries Points: 1075 More actions · Answer 8

This is what I have for the maintenance cleanup of the transaction log backup plan

EXECUTE master.dbo.xp_delete_file 0,N''E:\backups\tkcsdb\logs'',N''.trn'',N''2008-08-27T13:00:01''

I see older files in the directory that are supposed to be gone 🙁

michael.morse SSC Eights! Points: 891 More actions · Answer 9

I was having the same problem of the Cleanup task removing the older backup files. I noticed in one of the replies they showed their TSQL statement, and that is where I found my mistake. I had listed the file extension as '.trn' or '.bak' in order for the cleanup task to work right remove the '.' once I removed that it worked flawlessly.

Thanks

Irish Hall of Fame Points: 3958 More actions · Answer 10

Sometimes it is the little things that mess us up.

Michael, were you able to determine how the period got there in the first place? Was this a simple code error? :crazy:

Regards, Irish

michael.morse SSC Eights! Points: 891 More actions · Answer 11

Yeah it was my mistake of including the period. It works that way in SQL 2000 but not in the later versions.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 12

I will add that the original release (and one of the subsequent updates to it) of SP2 for SQL 2005 had some significant problems with maintenance plans/agent jobs. You need to get on a version above SP2.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Satnam Singh Hall of Fame Points: 3542 More actions · Answer 13

Consider an example wherein i have to delete a backup file older than 2 days.I have a database named example whose backup is happening in D drive.The backup file name is example_db_date.bak.Today being 21st Jan 2010, i want to delete those example db backup files present on D drive which are older than 2 days.

I use xp_cmdshell feature.

Note: Before implementing the below code please ensure that xp_cmdshell feature is enabled in SQL Server 2005 Surface Area Configuration.

DECLARE @DeleteOldDiff varchar(500);

SET @DeleteOldDiff = 'xp_cmdshell ''del "D:\example_db_'+

convert(char(8), getdate()-2,112) +'*.BAK'+'"''' ;

exec (@DeleteOldDiff)