Tempdb log file Usage

Seeing a test server grow the tempdb log file to 8 GB used out of 12 GB allocated over the course of a couple hours while load testing. The tempdb data files have almost no file space usage. Running DBCC OPENTRAN in tempdb, there are no open transactions for more than a few seconds. I'm running SQL Server 2005 and read snapshot isolation is off.

How can I determine what is consuming tempdb log?

Tempdb's log is different from logs in user databases, in a few ways.

In tempdb, only UNDO information is logged instead of REDO and UNDO, as transactions in tempdb are only ever rolled-back, never rolled-forward. This is because tempdb never has crash-recovery run on it, and so REDO information is never used. If these terms make no sense to you, see my TechNet Magazine article on Understanding Logging and Recovery in SQL Server. Note however that all operations that make changes to tempdb will be logged - tempdb is not 'non-logged'.

Tempdb's log does not clear/truncate (see article again) quite like other database logs. In tempdb, the log will truncate on checkpoint (as in regular SIMPLE recovery model), but a checkpoint does not occur automatically every so often like it does in user databases. In user databases, the checkpoint occurs to try to limit the time it takes to run crash-recovery (by forcing dirty pages to disk and thus limiting the amount of REDO recovery that must be performed) and to batch up page updates to reduce the overall amount of I/O that's performed.

As such, checkpoints in tempdb only occur when the tempdb log usage reaches a certain proportion of the total size of the log, around 70% because recovery time is irrelevant.

To summarize, your tempdb log is behaving exactly as expected.

Hope this helps.

The log for tempdb grows just like any other transaction log does. Everything gets logged until the next checkpoint at which point the log entries are marked as being able to be overwritten. By default the checkpoint runs every minute or so, so if you have a lot of stuff happening in the tempdb (and since you are load testing there's probably a lot of stuff going on in the tempdb) then the log will grow like this.

If your load test matches your production load then you can expect your production tempdb log to grow to this size as well.