Error: 9002, Severity: 17, State: 4 – The transaction log for database ‘tempdb’ is full due to ‘ACTIVE_TRANSACTION’

Problem:

This was a compound problem I had on a production SQL Server 2014 instance.  First was someone who scheduled a couple of load jobs at the same time which grew the tempdb to filling its hard-drive.  Next was a well intentioned rookie DBA that shrunk the tempdb log file and turned off auto growth, in order to stop the alerting that notified us that the hard-drive was filled.

Shortly after that we started getting the following alerts (also in SQL Server’s error log):

Error: 9002, Severity: 17, State: 4.
The transaction log for database ‘tempdb’ is full due to ‘ACTIVE_TRANSACTION’.
Error: 9002, Severity: 17, State: 4

Resolution:

Note:  images and example was done on a test system, and TempDB should never be put on be put on the C-drive for a production environment.

To correct this issue, I needed to “right size” (shink) the TempDB data files.  I used the following query to find the current data file sizes:

--
-- Query to show the Logical Name, Physical File Location, 
-- & the file size in MB for TempDB
--
SELECT 
sys.master_files.name as 'Logical Name', 
sys.master_files.physical_name as 'Physical File Location', 
(sys.master_files.size*8)/1024 as 'Size in MB'
FROM sys.master_files WITH (NOLOCK)
JOIN sys.databases WITH (NOLOCK) 
on sys.master_files.database_id=sys.databases.database_id
WHERE sys.databases.name = 'tempdb' and sys.master_files.type = 0
GO

Query to show the Logical Name, Physical File Location, & the file size in MB for TempDB

My normal attempts to shrink the data files for TempDB failed.  What I found that if I cleared the plan cache (using DBCC FREEPROCCACHE), and ran the shrink statements all at once, the data files did shrink.  Note that I was not excited about clearing the plan cache on a production system, however the jobs were failing because the TempDB issues.  Using the query results from above to give the logical file name, and adjusted the file size in MB using (DBCC SHRINKFILE).

USE TEMPDB;
GO
-- Clear the Plan Cache
DBCC FREEPROCCACHE;
GO
-- Rightsize the TempDB files 
DBCC SHRINKFILE(tempdev, 1024);
GO
DBCC SHRINKFILE(tempdev2, 1024);
GO
DBCC SHRINKFILE(tempdev3, 1024);
GO
DBCC SHRINKFILE(tempdev4, 1024);
GO

Shink TempDB Files

Once this ran, I reran the query from above to verify that the files did shrink.

Verify shrunk TempDB data files

Also I verified the free space on the drive used by TempDB.  Then I resize the TempDB’s log file, made sure that is growth would no longer fill up the hard-drive.  Finally I made sure auto growth was turned off for data files on TempDB now they were right sized.

My example in this post is a very small TempDB, however in my production environment TempDB is over 100 GB.  Anyone running something on this production environment that requires more then that 100 GB in TempDB is going to have to explain why they need all that space & probably will pay for the addiction storage for TempDB’s growth.

Thanks to Tara Kizer who’s post gave me the idea of clearing the plan cache.

 

Comments are closed.