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

Avyayah 1,251 Reputation points
2021-01-31T21:49:58.1+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,887 questions
{count} votes

Accepted answer
  1. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-02-01T05:44:15.483+00:00

    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.

    0 comments No comments

0 additional answers

Sort by: Most helpful