Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 4 down vote accepted

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.

share|improve this answer
    
I didn't realize tempdb log behave differently than a user database -- Thanks! –  Chad Miller Jun 24 '09 at 17:42
    
Yup - it's not commonly known. Cheers –  Paul Randal Jun 24 '09 at 18:27

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.

share|improve this answer

You can use SQL Profiler to see what users/applications are doing things that require tempdb storage.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.