May 22, 2009 at 4:50 pm
Ok, I've read other posts similar to the subject of this thread. I didn't want to hijack other threads so I started a new one to post my question.
2 months ago I moved a database from an old slow server to a brand new one. I turned off all the backups to this specific server, gave it a new name and configured the application to point to the new server and database. I'm keeping this server on until it dies to do some play with reporting and simple BI projects I want to test.
4 days ago I finally get back to this old server and find the mdf file is 677Mb (which is where I'd expect it to be) but the ldf file is approx 49GB. I had 9Mb of free space on that drive. This server is not being used and I know nobody is using this DB. I didn't have much time to play with it because I was headed to a training so I deleted a bunch of text files on the old server that had been moved to the new server....about 7 GB worth. I got back yesterday and found 191MB of free space left. I started googling and didn't find anything. An hour later it was down to 10MB free so I quickly detached the database. I don't have any room on this server to backup a transaction log this size. All I want to do is truncate it. I want to keep the database to dink around with. How do I accomplish this? We are on 2005 and I've found out that shrinkfile doesn't do what I thought it would do. Is there syntax to just empty out the transaction log? I don't need anything inside of it.
May 22, 2009 at 5:07 pm
Nevermind, I changed the recovery model to Simple and did a shrinkfile and it shrank it down to 1MB.
May 22, 2009 at 8:52 pm
Just to follow up - you need to verify that you have regular transaction log backups being performed on your live server. My guess is that when you shut things down on the old server, you disabled the transaction log backups - but did not disable the index rebuilds or some other process.
That is why I say you need to verify your live system. If you didn't have transaction log backups on the old server, I am guessing that you don't have them on your live server and eventually you'll end up in the same situation.
I am hoping I am completely wrong here.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 23, 2009 at 8:50 am
Be sure you put it back in full mode and then do as Jeffrey suggested.
Until you perform a log backup (in full mode), the log keeps growing.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 27, 2009 at 1:29 pm
Hi steve,
Can you please explain me about this.
All you guys are saying when the disk is filled with the transaction logs you are saying to take the back up and then change to mode to simple and then shrink the database and then switch the mode back to full backup.
I want to know like when you switch to simple no tlogs will be saved when the transactions are going on in that database right? at this particular time you will lose the tlogs. I'm i right?
Can you plz tell exactly what i have to do when i see tlogs are filled. I just confused by reading so many answers. Please.
trying to learn the best.
Thanks,
sam.
May 27, 2009 at 1:32 pm
Please read through this - Managing Transaction Logs[/url]
If you have any questions afterwards, do ask.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply