Monitoring Long Running Transactions in TempDB

TempDB database plays a major role in SQL Server. Therefore, it is extremely important to monitor the health of the TempDB database. One of the major challenges in TempDB is maintaining it's log file. If there are transactions that use the TempDB and if those are long-running transactions, there can be situations where the log file will grow. Since these transactions are not closing, log space will not be returned and the entire server will not be able to run queries that use the TempDB. 

Recently, one of the Clients had a similar problem. One query was running for more than four days and it had consumed TempDB log file. This has caused empty disk space and the entire server is halted for operations.

In this situation, the easiest and laziest thing to do is the restart the server. Restart will kill all the transactions and return TempDB back to the original size. This is not something that you can do for a system of 24x7. 

However, we choose not to restart but to identify the long-running query from the following simple query.

SELECT  se_tr.session_id,

sec.login_name,

trn.database_transaction_begin_lsn,

trn.database_transaction_begin_time,

trn.database_transaction_log_record_count,

 trn.database_transaction_log_bytes_used,

 trn.database_transaction_log_bytes_reserved,

 t.text,

 q.query_plan

FROM sys.dm_tran_database_transactions trn

INNER JOIN sys.dm_tran_session_transactions se_tr ON trn.transaction_id = se_tr.transaction_id

INNER JOIN sys.dm_exec_sessions sec ON se_tr.session_id = sec.session_id

INNER JOIN sys.dm_exec_connections con ON con.session_id = sec.session_id

LEFT OUTER JOIN sys.dm_exec_requests req ON req.session_id = sec.session_id

CROSS APPLY sys.dm_exec_sql_text  (con.most_recent_sql_handle) t

OUTER APPLY sys.dm_exec_query_plan (req.plan_handle) q

WHERE trn.database_id =DB_ID('TempDB') 

This gave the option to identify the long running query and we killed the relevent session. With that, TempDB log file was emptied and by shrinking the tempdb log file, we were able to gain the disk space. 

Further, we took a pro-active decision by enabling an alert, so that if a query runs for more than 8 hrs (configurable) that will be altered the DBA so that he can kill the session straightway.