Q:

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?

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.

A:

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.

