February 6, 2009 at 2:31 pm
Okay, I'm not really a DBA (yes, I've said this before). We have a new 2008 Server machine that is running SQLServer 2005. We have SQLServer installed on the C: drive and store all Databases and backups on the D: Drive. I used the Maintenance Plans to build jobs for backups and want to be able to keep the backup directories cleaned up - storing no more than 2 weeks worth of files (We copy these files to other backup devices on a regular basis so we only need 2 weeks on this machine). The problem I am having is that I don't seem to be able to get the old .trn and .bak files to clean up. I have been running things since October 2008 and all those .trn and .bak files are just piling up on me no matter what I try. I only have 7 databases on the thing right now and I just deleted over 14GB of backup files that should have been cleaned up on their own if I knew what I was doing!
Can someone help me figure out why I can't get this to work - I used to be able to do it??
~mj
February 6, 2009 at 3:06 pm
What have you tried so far? And, what version of SQL Server 2005? Check the version for both the database and the client tools you are using.
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
February 6, 2009 at 6:01 pm
February 6, 2009 at 9:32 pm
If you are using 2005 SP1 and you are using the maintenance plans to delete your files then you may be running into a known issue that was fixed in SP2. The maint plan uses xp_deletefile (or something named similar) which reads the file header to ensure that the file is either a backup file or a backup log file. In SP1 the header of the file was slightly different making xp_deletefile totally ineffective.
I may be off on some of the facts as I did some research for an article on this quite a while ago (sorry never published it) and much of this has slipped out of memory. 🙂
Poke around on the web for xp_deletefile and you will find most of the information that I am referring to.
If you are using something other than the maintenance plan post what you are using so that review and suggestions can be made.
One suggestion, check out the Scripting Guys on Microsoft's website as they have some VBScripts which can be used to delete files. I actually use VBScript as I can check to ensure that the file has been backed up to tape (ready to archive bit value = 32) prior to me removing the file. Just an extra slice of safety. 😎
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 7, 2009 at 8:30 am
Thank you to all who replied.
ps - it does look like I can use the FORFILES to do what is needed - I will give this a try on Monday.
David - I also appreciate your input and will check that out as well. I do believe that the server in question is just on SP1 so perhaps that is part of the issue with the Maintenance plan clean up stuff I was working with.
I will post results next week.
Again, thank you VERY much!
mlj
update: 2/9/09 I have played with the xp_cmdshell suggestion and this is working for me. Thank you!
~mj
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply