I want to start this blog out with a Warning Dear Reader.  The technique that I am going to describe to you today is how to use Truncate_Only to NUKE your transaction log.  This is a topic that a quick search on will find you this Blog by Brent Ozar(@BrentO| Blog) and this MSDN forum conversation that has a link to a Paul Randal (@PaulRandal | Blog) Blog on why you should not use this as well.

"So Balls", you say, "Why are you blogging about something we should not use?"

Great question Dear Reader, the answer lies in the details.   With the one place that I would say this is okay to use.

So let's start off first on why this is bad, so you will understand that you should use this with caution.  Your Transaction log is an essential part of your database.  This is an understatement.   This is like saying your Brain is an important part of your body. 

You cannot operate without it.  Period.  It remembers everything that you have done in your life, Transactions that have been committed and flushed to disk for the Database.  If you get knocked out when you wake up you start piecing what you did and how you got there.  In the database work we call this Recovery.

You cannot recover your database if your transaction log is gone, the database won't start up.   Not only that if you have a very important database, you cannot loose any of the transactions that you commit, ie you need to remember everything.   If you have a pharmacy database that track prescriptions, a bank database that tracks customer deposits, or a hospital database that tracks what was given to a patient and when.   You cannot lose any of that information. 

When your database is this important it SHOULD be in Full Recovery Model and we should be backing up our transaction logs in accordance with our Service Level Agreements, SLA's,  in order to meet our Recovery Time Objective, RTO.   If you use the command I will speak about in a moment on a Fully logged database it will wipe out your Transaction Log, good bye memories.  The structure will still be there, but this will make it to where you lose your ability to recover to a point in time.

"So Balls", you say, "What's a Checkpoint and what in the SQL Internals does it have to do with my database?"

Great question Dear Reader, man you are on top of your game today!  A checkpoint is a process that flushes Dirty pages from the buffer pool to the disk.   Sticking with the biology terms it takes what your eyes are seeing and flushes it to your brain to make, commit, the memory.

In SQL 2000 there was a known issue where occasionally the checkpoint process would go to sleep.  So why is this a bad thing?  Well your transaction log tracks the transactions that are flushed to disk, and when checkpoint went to sleep the transaction log would continue to grow and fill.

In a situation like this what should you do?  Simple open up Query Analyzer or SSMS and simple go to the database in question and type "Checkpoint".  That simple.  Checkpoint the database, execute a DBCC SQLPERF(LOGSPACE) and examine the free space for your database log to see that you now have plenty of room.

So when should you use the unspeakable command that I'm speaking of? 

You get a call for a server, it just so happens to be a SQL 2000 server on SP 4 (awe hotfix or security patch that takes you up to build (8.0.2055 i've seen it in both).  You've gotten reports that the TempDB has run out of log space.

You log in when you type:

You get an error stating that a Checkpoint could not be committed because the transaction log was full.  At this point you have queries that are failing because tempDB is used, ALOT.  In order by, sort, and group by queries.  By Queries that need temp tables, variable tables, and work space tables.

Your only option at this point would be to restart your instance.  Why restart your instance because TempDB is destroyed and Re-Created each time your instance is restarted.

So before you do that, what can you do?  You can execute the following command.  Again only on a database where you do not care AT ALL about the transactional consistency of the database log.  Also you want your database to be in Simple Recovery Model by default.  And you are on a critical SYSTEM DATABASE that is destroyed and recreated on each restart.  Before you take an outage of your system try this.

This command was depriceated in SQL 2005 and is not there in SQL 2008.  There is a work around, but I'm not posting the work around for SQL 2008.  Why because in my humble opinion Dear Reader you would only want to do this when the checkpoint has gone to sleep in the tempdb.  While I have see this A LOT in 2000.  I have yet to see it in 2008 and above.

So once again, be cautious and my recommendation is to only ever use this as a last ditch method of freeing up TempDB space as a means to avoid a reboot.

Thanks,

Brad