Why is the tempdb logfile never empty or has less log space usage

Question

Tempdb log space usage is 28% - 50% full although there are no open transactions.
dbcc opentran
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

But the tempdb has log spaced used 28%
When will it be empty or what query can I run to check why it is not empty.

Answer 1

Hi @Avyayah ,

Suggest you manual checkpoint tempdb to clear out the log. TempDB is in 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. Automatic CHECKPOINT operations on tempdb are only done when the log usage reaches 70%.

Then run the below query to analyze the transaction log for tempdb. Looking at the operation in the log.

SELECT  
     [Current LSN],  
     [Operation],  
     [Context],  
     [Transaction ID],  
     [Log Record Length],  
     [Description]  
FROM fn_dblog (null, null);  
GO  

Please refer to the blog Understanding data vs log usage for spills in tempdb.

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.