SQL Server 2000 Massive Transaction Log Fix

Ahhh the sweet smell of a full hard drive - isn't wonderful, NOT (think Borat). Ok so today I was checking out a computer running SQL Server 2000 when I noticed that an application was throwing errors that the disk was full.

I took a look and sure enough it was! I knew this box was getting low on space for a while, but I ignored it (not a production box, just a test). I tried to clean up space the usual windows way, run the low disk space cleanerupper. Then I went through and looked for logs in various applications and wiped them, but this was not enough.

Then I did what is one of the more clever things to happen to the windows search - a search based on file size. It has been in windows since at least 2k, but I'm pretty sure it was there in 98. I looked for files greater than 100mb and sure enough an 8GB file popped up at the top. What was it? well my SQL Server 2k transaction log (.LDF) for a database that has fairly high traffic. Since this is a transaction log in general you don't want to just wipe them. I did a couple google searches (<rant>btw Microsoft's MSDN site sucks for SQL server - at least in this instance. They had a dozen articles on shrinking the db, but not one that actually showed a command to do it. Anyone can talk about theory, but just a simple example would be nice.</rant>).

Ok well now it has been about a half hour and I came across:

http://www.eggheadcafe.com/community/aspnet/9/10012663/very-easy-way-to-truncate.aspx

This was perfect (and at least 2 pages in on my google search). From the page:

In SQL Server 2000 Query Analyzer run the following command (doesn't matter what db you are in):
backup log [database name] with truncate_only
(don't include the square brackets)

Open Enterprise Manager and navigate to your database:
--Right click on the database
--Highlight 'All Tasks'
--Choose 'Shrink Database...'
--On the bottom of the window on the right, click the 'Files' button
--In the drop down at the top make sure you choose the '_Log' option. By default it is the '_Data' option
--Leave the shrink action as: 'Compress......'
--Click 'OK'

At this point depending on the size of the file, it may take a minute or two or more and then it will give a success message. I took a look at my LDF file again and it went from 8gb to ~2mb.

Success!! Now rejoice by clicking the play button below (this is in my head every time I do something successfully)!