How can I list all sessions currently connected to a specific database using dynamic management views.
To be more precise I want to list all, including sleeping, sessions connected to a specified SQL 2008R2 database. I want to avoid using sys.sysprocesses since it is to be deprecated so you should be able to use dynamic managemnent views instead. The aim is to have a similar output to that of SP_WHO2 which I am avoiding because it also uses sys.sysprocesses. I guess at some point MS are going to have to rewrite this - They still have as of SQL 2012. The main issue is getting the database_id of a sleeping session. I know the SQL 2012 version of sys.dm_exec_sessions includes a database_id column but it was not there in previous versions i.e. 2005, 2008, 2008R2. Joining to sys.dm_exec_requests is not the answer because that only shows active sessions. Does anyone know how to achieve this?
You could outer join `sys.dm_exec_sessions` onto `sys.dm_exec_connections`, then outer apply that to `sys.dm_exec_sql_text` using the `most_recent_sql_handle` which will return a `dbid` select * from sys.dm_exec_sessions s left join sys.dm_exec_connections c on s.session_id = c.session_id outer APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS st That seems to give me the closest equivalent on my local machine
If you are trying to avoid sysprocesses, which you should be as you say because it is to be removed from the product, then I would recommend the almost exact replacement created by Jason Strate which uses DMVs as you mention. It uses the DMVs from @kev riley 's answer and a few more to give as close an approximation as you can get of sysprocesses from the DMVs as they stand.