Here is what to do when the tempDB log file runs out of space. You will not be able to run any sp_Who* queries. You will likely get this error shown below:
1 2 3 4 5 |
Date 3/31/2014 12:04:01 PM Log SQL Server Agent (Current - 4/1/2014 12:30:00 AM) Message [298] SQLServer Error: 9002, The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'. [SQLSTATE 42000] (ConnIsLoginSysAdmin) |
First, try running the DMV below to get the session data and see who the culprit SPID is:
1 2 3 |
SELECT * FROM sys.dm_exec_sessions des WHERE des.status NOT IN ('sleeping') |
…next see what open transactions are still running. The results will show you the SPID:
1 |
DBCC OPENTRAN WITH TABLERESULTS |
… next kill the SPID:
1 |
KILL spid |
…now you can shrink the file (assuming it has been set to a max growth size and thus filled up:
1 2 |
-- Shrink the truncated log file to 1 MB. DBCC SHRINKFILE(templog, 1); --file_name is the logical name of the file to be shrink |
Views – 14884