SQL Server – Transaction log for database ‘tempdb’ is full due to ACTIVE_TRANSACTION

Tempdb is full due to active transaction

Tempdb database is part of SQL Server System database and gets created every time SQL Server Service starts. Tempdb stores temporary operations (like sorting and grouping data output etc) and tables, it stores lots of  information in cache to improve query performance in sql server.

Whenever transaction log for ‘tempdb’ database gets full, try to shrink log file to release space back to disk. If it is not production then best thing is to restart SQL Server service that will reset tempdb to its default size.
We got similar issue with TempDB database some days back, User were unable to execute query on production due to below error.

Msg 9002, Level 17, State 4, Line 1
The transaction log for database tempdb is full due to ‘ACTIVE_TRANSACTION’.

We tried shrinking of tempdb log file using below command but no luck.

DBCC ShrinkFILE(tempdev,2048)

FIX : Tempdb is full issue without restarting sql server services

As shrinking was not working so to reclaim space for tempdb database. To avoid restart SQL Server Service, Executed DBCC FREEPROCACHE

DBCC FREEPROCACHE

It will clear the procedure cache information that will release some space now try to shrink transaction log to reclaim free space to disk.
Remember this method will degrade some performance of queries at fist time due no cache information, SQL Server will automatically create new cache for queries and stored procedure again.

Warning : before shrinking make sure that no active transaction should present in tempdb otherwise shrinking will fail and tempdb database may get corrupt !

Now shrink tempdb database

DBCC ShrinkFILE(tempdev,2048)

Leave a Reply

  

  

  


+ 7 = 16