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?
Best Answer
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.