TempDB Transaction log not releasing space

Question

Every time I complete a transaction in tempdb, the temp log grows, however the log doesn't seem to release the space once the transaction has completed.The log usage percent is 43.4% but seems to increase in around 4% increments.

When I look for open transactions (sys.dm_exec_sessions), there are none. Yet the log_reuse_wait_desc in sys.databases shows 'ACTIVE_TRANSACTION'. When I query the sys.dm_tran_active_transactions DMV, it shows only work table.

I'm unsure of how I can release this space from the tempdb transaction log.

I've just run a manual checkpoint and that seems to free up the space so I'm not sure as to why the space isn't being freed up on it's own

Answer 1

The log_reuse_wait_desc is only updated when SQL Server attempts to clear the log. In other words, it's not necessarily a "live" view of whether the log can be cleared or not. Paul Randal explains that in his blog post "Why is log_reuse_wait_desc saying LOG_BACKUP after doing a log backup?".

In the case of tempdb, which uses the SIMPLE recovery model, SQL Server attempts to clear the log whenever a CHECKPOINT is issued. This is why doing a manual CHECKPOINT frees up the space, as you mentioned.

SQL Server is a lot less aggressive about doing automatic CHECKPOINTs on tempdb, both because it uses the SIMPLE recovery model, and because it is by definition "temporary" information. So there's no need to constantly persist it to disk. This is a performance optimization specific to tempdb.

Per another blog post by Paul Randal, the details are as follows:

• automatic CHECKPOINT operations on tempdb are only done when the log usage reaches 70%
• dirty pages are not written to disk on automatic CHECKPOINT (its primary purpose it to clear the log)

The "work table" rows in dm_tran_active_transactions are normal behavior, and shouldn't prevent the log from being cleared. You can read more about that in this Q&A:

SQL Server sys.dm_tran_active_transactions long running transaction worktable