Issue:

The transaction log for database ‘tempdb’ is full due to ‘ACTIVE_TRANSACTION’

Fix:

Restart server to clear up tempdb.

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.

Script to shrink tempdb database files

   use tempdb
   go

   dbcc shrinkfile (tempdev, 'target size in MB')
   go

   dbcc shrinkfile (templog, 'target size in MB')
   go
Last modified: October 5, 2021

Author

Comments

Write a Reply or Comment