Adam Angelini (5/27/2015)
Great reply, Jacob. I need to start referencing the documentation in my answers.
Thanks much!
dastagiri16 (5/27/2015)
Thanks lot....and sys.sysprocess also takes the info from log ?
The deprecated sys.sysprocesses, along with sys.dm_exec_requests and sys.dm_exec_sessions will show the session running only a SELECT within an explicit transaction as having an open transaction.
You can check the open_tran column of sysprocesses or the open_transaction_count column of the other two DMVs. In the example scenario I posted above, you would only be able to see the first query in sysprocesses and sys.dm_exec_sessions, since the first query wouldn't be currently executing, and thus would not show up in sys.dm_exec_requests.
Cheers!