New columns in exec_sessions DMV in SQL 2012

While reproducing the issue with DBCC OpenTran I wrote about earlier this week here I found a pleasant addition to the sys.dm_exec_sessions DMV. In SQL 2012 we have 3 new columns added

Open_transaction_count: which shows the number of open transactions for the session whether or not these transactions are reading or writing. So an open transaction that you cannot see in DBCC OpenTran will be counted here. Similar to open_tran in sys.sysprocesses.

Database_id: The long awaited column in exec_sessions is finally here. No need for sp_who2 any more to know which session connected to which database. Of course to show to database name you need to use db_name() function.

Authenticating_database_id: This columns was added to support the contained databases. It contains the database_id of the database that did the authentication for the session. If it's a login then it should show the database_id for the master database (1) otherwise it will show the database_id of the contained database that has the user with password. The sys.dm_exec_sessions documentation documented this column as it will have 0 when it's a login but I tested it and it shows 1. I'll try to file a documentation bug for this one.

 

~M.S.