How can I figure out when a session opened a transaction to debug a session being blocked by a sleeping session that has an open transaction?

Question

I can reproduce a problem where my SQL Server stops responding. I can see that one session is blocked by another session. When I look at the status of the blocking_session_id, I see that it is 'sleeping'. If I look at what sessions have open transactions I see that the sleeping session has an open transaction.

I have been using SQL Server Profiler to try to determine when it opened the transaction, but I have not been able to figure out how to make the trace log contain any events that look like a transaction being opened.

Answer 1

sp_WhoIsActive by Adam Machanic is quite handy, and is easy to find blocking information with.

Just running with EXEC dbo.sp_WhoIsActive @get_locks = 1, @get_plans = 1

I found blocking on my uh... production server.

Answer 2

You should be able to use a couple of system views to get you where you want to go:

SELECT [dtat].[transaction_begin_time]
FROM [sys].[dm_tran_active_transactions] AS [dtat]
JOIN [sys].[dm_tran_session_transactions] AS [dtst]
    ON [dtat].[transaction_id] = [dtst].[transaction_id]
WHERE [dtst].[session_id] = @spid