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.

2 Answers 2


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.



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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.