Preventing Transaction Log Fires

When I teach SQL Server, I often have to clear up some misconceptions about the Transaction log. Here are some of the key facets to keep in mind.
A data update (say editing the phone number of a contact) triggers a series of actions.
1) The relevant 64KB chunk of the database (called an extent)  is loaded from the hard drive into memory.
2) Once in memory, the actual data can be changed. During this time, the record is locked, so no one else can simultaneously make a different change.
3) Before the record can be unlocked, the database will record this exchange of data (called a "transaction") into the transaction log.
4) The record is unlocked IN MEMORY, and future reads and writes will be pulled from MEMORY (not the disk)
-- Notice that we have not written the "extent" back to the database file yet.
5) Later on, when enough data has been written to the database to make it worth it to SQL, the updated extents will be written back to the database file. This process is called a "checkpointing the database." This always occurs before you backup the database or if you issue the CHECKPOINT command in Transact SQL.

Why is the writing to the database delayed? Image moving from lovely Portland, Oregon to Toadsuck, Arkansas. You could load all your stuff into a moving van, and once it was full, send it on its way. Very efficient. Or... you could load your stuff into a VW Bug, send it off, and when it gets back load it with more stuff, send it, and so on. Not so efficient now! We want the "moving van" solution. The transaction log is going to act like a packing slip for this moving van.

If there should be a loss of power to the system transactions that occurred in memory are lost if they were never checkpointed... so what happens? Reports could have been written based upon that data that was NEVER WRITTEN TO THE HARD DRIVE! Never fear, transaction log is here! On bootup the system recovers all the transactions written AFTER the last checkpoint!

If you are developing an application for a customer that uses a sql database that may be unmanaged, I would always recommend setting the default recovery mode to SIMPLE, so that log files will act in a circular manner.  However, in order to be able to restore to a point in time you need to set the model to FULL. This means that the transaction log is never circular unless the log has been backed up. Only then will the log file content will be automatically overwritten. 

However, sometimes you realize, "Hey! My log file is 26 Gigabytes! Now what?" Here are two methods to quickly grab your space back that the transaction log has been using.

First, stop the transaction log file (.ldf) from growing by setting the database mode to simple before going any further.

1. Open Enterprise manager
2. Right click on the database,
3. Choose properties,
4. Select Options,
5. Set model to simple,
6. Click OK.

Now you need to shrinking the transaction log file (.ldf). This only works if you have free entries in the log file that are available to overwrite. You accomplish this by (a) setting the recovery model to simple (see previous steps) or (b) backing up the transaction log.

1. Open the Enterprise manager
2. Right click on the database,
3. Choose All tasks,
4. Choose Shrink database,
5. Choose Files,
6. Select the transaction log file,
7. Click OK.

Optionally, you can now set the database back to the FULL recovery mode if you want to have full transaction log recovery capabilities.

OR the complete super-shrink with an Offline Detach

1. Always take a full backup before a detach.
2. Open Enterprise manager
3. Right click on the database, All tasks, Detach database, OK.
4. Delete the transaction log file.
5. Right click on databases, All tasks, Attach database, Select the .mdf file, OK, Yes (to the create new log message).