What hanging transaction in tempdb mean?

61 Views Asked by At

I'm trying to troubleshoot what is causing timeouts when querying specific table in production.

I suspected that there are some uncommitted transactions, so I ran following query:

SELECT trans.session_id AS [SESSION ID]
    ,ESes.host_name AS [HOST NAME]
    ,login_name AS [Login NAME]
    ,trans.transaction_id AS [TRANSACTION ID]
    ,tas.name AS [TRANSACTION NAME]
    ,tas.transaction_begin_time AS [TRANSACTION BEGIN TIME]
    ,tds.database_id AS [DATABASE ID]
    ,DBs.name AS [DATABASE NAME]
FROM sys.dm_tran_active_transactions tas
JOIN sys.dm_tran_session_transactions trans ON (trans.transaction_id = tas.transaction_id)
LEFT OUTER JOIN sys.dm_tran_database_transactions tds ON (tas.transaction_id = tds.transaction_id)
LEFT OUTER JOIN sys.databases AS DBs ON tds.database_id = DBs.database_id
LEFT OUTER JOIN sys.dm_exec_sessions AS ESes ON trans.session_id = ESes.session_id
WHERE ESes.session_id IS NOT NULL

It returned two rows with the same [Session ID], one for database name tempdb, second for my application's database:

SESSION ID, Login Name, ... TRANSACTION BEGIN TIME, DATABASE NAME,
218,        MyAppSQLUser,   15 minutes ago,         tempdb
218,        MyAppSQLUser,   ...,                    MyDatabase

KILL 218 solved the timeouts

Does the tempdb row indicates something? Why it's event there

What are next steps? How can I trace the transaction to my source for in the future, so I can find out, what's causing it?

0

There are 0 best solutions below