HOW TO DELETE OLD BACK UP FILES AND TRANSACTION LOGS IN SQL 2005

SQL 2005 DOES NOT HAVE THE SAME FEATURE THAT WAS IN 2000 TO DELETE OLDER FILES OLDER THAN CERTAIN DAYS. I DO HAVE A JOB THAT CREATES A FULL DAILY BACKUPS BUT SINCE I DON'T HAVE ENOUGH SPACE, I WOULD LIKE TO FIND A WAY TO DELETE ALL OLD BACKUP FILES OLDER THAN X AMOUNT OF DAYS AND ALSO DELETE TRANSACTION FILES OLDER THAN X AMOUNT OF DAYS. IS THERE A TOOL TO DO THIS. CAN ANYONE HELP PLEASE.

EMADKB

The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans.|||

Unfortunately - it does not seem to control deletion of the backup files!

It cleans out information from Datebase logs - but that is not really the problem.

At least from what we see - it does not delte the older files.

We have a cleanup running but older files do not go away.

Bob

|||

It's happening to me too. In our maintanance plans we have clean up tasks set to remove old files but no files are ever deleted. Even running EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006 13:12:32' manually still did not remove any files.

Anyone find a cure for this?

thanks!

|||The maintenance cleanup task will work but you need to specify the folder the files are in and the appropriate extension. SP1 does have an option for "include 1st level folders" in this case it will search the folder you specify and one level down. Also I seem to remember that for the backup files or the logs I needed the / at the end of the folder specification and one of them I didn't. We no longer use maintenance plans for backups and I don't remember which it was. Good Luck.|||There's definately a bug in the Maintanance Plan Clean up task, it will not delete any files. Until they fix it we'll have to do it via a SQL command.|||

There are multiple Microsoft Connect items around Maintenance Plans that we've addressed in SQL Server 2005 Service Pack 2. I am not sure why the Maintenance Plan clean up task is not working for you. In our labs, it is definitely working with pre-release service pack 2 bits.

Add Cleanup Task to Wizard
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126800

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

We've installed SP1 and SQL 2k5 does not delete the old .bak nor .trn backup files. SP1 gave us the checkbox for sub-folders, but it still won't delete the old backups.

Unfortunately my 60+ production databases aren't setup in your Lab and I don't have pre-release service pack 2 bits, so I need to know what MS is going to do to help people in the real world, like me, delete the old backups?

|||We are also experiencing this problem with our production SQL 2005 SP1 databases and it is frustrating. Thexp_delete_file SP reports successful completion but does nothing.
|||

I've never been too fond of Maintenance plans, there is only a limited amount of control with them. If I want the delete function for old backup files I use the -DelBkUps switch in the sqlmaint utility. Put this into a multi step SQL Agent job and you have ultimate control.

BOL - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/937a9932-4aed-464b-b97a-a5acfe6a50de.htm

|||

Change your code from

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006

to

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'bak',N'10/24/2006 13:12:32'

That worked for me it seemed that '.BAK' is not accepted and 'BAK' as extension is.

Daan Stam

|||I added the \ after the unc path after I browsed to D:\ Sql Server Backups a folder that I created and the clean up worked for both bak and trn. We are running 2005 sp1, by default the path is incomplete when you click on the folder you want. Thanks for posting the fix|||Why does Microsoft have to keep changing things to make it more difficult? Why didn't they just keep it the same as sql 2000. This is ridiculous!