Truncating SQL 2005 Log Files

Had a fun scenario recently where a customer (a Casino!) ran out of disk space on their SQL 2005 Server.  This in turn caused all their slot / video poker / whatever gambling machines to stop working.  Turns out old Bill Shakespeare had it all wrong, because it turns out that Hell Hath No Fury Like A Gambler Scorned!

Long story short - their SQL log file grew to gargantuan proportions, and yours truly had to whip them back into shape.  Here's how it went down!

1. Run the following stored procedure: "use your_db_name"
2. Followed by: "exec sp_helpfile".  This will return the physical names and attributes of files associated with your DB.  Record the DB and log filenames, without the path and extension
3.  Enter the folowwing commands
1. USE your_db_name
2. GO
3. BACKUP LOG your_db_name WITH TRUNCATE_ONLY
4. GO
5. DBCC SHRINKFILE (your_dblog_filename, 1) 
6. GO
7. DBCC SHRINKFILE (your_db_filename, 1)
8. GO
9. exec sp_helpfile

Step 9 should output the same info has Step 2, you can now compare the filesizes to see if the process was succesfull.

You might get an error "Cannot shrink log file because all logical log files are in use".  In that case you can follow the instruction here to resolve.  I've detailed the steps below, if you're too lazy to follow the link.

1. Open SQL Enterprise Manager
2. Right-click on the database you want to shrink and click Properties
3. from the Data Properties go to Options.
4. Set the Recovery Model to Simple and click OK and try to shrink the database

Your Database and Database log files should now shrink succesfully!