question

StuKay avatar image
StuKay asked

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?
dynamic-management-viewsessions
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This site works by voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
StuKay avatar image StuKay commented ·
I thought I did with Kev Riley's answer straight after I read it!
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
I went ahead and marked it. It wasn't. You can tell it is now because the outline turns green.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

StuKay avatar image StuKay commented ·
That looks to be it. Not familiar with the OUTER APPLY part of the statement but something I will now look into further. Also missed the most_recent_sql_handle column. I have been looking in all the wrong places. Thank you very much. Excellent answer.
0 Likes 0 ·
JohnM avatar image
JohnM answered
You could try Adam Machanic's "sp_whoisactive" instead of trying to re-invent the wheel. Just a thought. ;-) http://sqlblog.com/blogs/adam_machanic/archive/tags/who+is+active/default.aspx I use this all the time in our environment while out any fear of performance issues. Hope this helps!
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

StuKay avatar image StuKay commented ·
Hi John I am familiar with the sp_whoisactive stored procedure but it did not fit my requirements. There are many ways to see active sessions but I specifically wanted to see non active sessions. I also wanted to be able to do this via one T-SQL statement rather than a stored procedure which would have to be installed onto all of my SQL Servers (Plus the maintenance overhead of having to update them all anytime changes are made to it). It was not so much a re-inventing the wheel task but more to do with understanding how the wheel turns. Thank you for the feedback anyway.
0 Likes 0 ·
JohnM avatar image JohnM commented ·
Thanks for the follow up! =)
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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. http://www.jasonstrate.com/2012/12/lost-in-translation-deprecated-system-tables-sysprocesses/
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

StuKay avatar image StuKay commented ·
I had discovered this and it is a good attempt. My only issue is it is SQL 2012 code using the new DMVs which have extra columns (Especially the database id). I was specifically interested in a version based on SQL2008R2.
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
you can convert it to fit previous SQL Server versions... alter es.open_transaction_count AS open_tran to er.open_transaction_count AS open_tran and alter es.database_id to er.database_id
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.