Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vr8ce
Starting Member
23 Posts |
 Posted - 2010-12-03 : 12:17:08
|
| I'm not sure if this is an admin or a Transact question. I'll start here, feel free to redirect me if necessary.Generically, is it possible to duplicate all of the columns in sp_who2 strictly from the sys.dm_* management views, i.e. without using sys.sysprocesses?Specifically, is it possible to get the database ID for a session strictly from the sys.dm_* views?sys.dm_exec_requests has a database ID, but if a session is inactive, e.g. sleeping, etc., then it doesn't have an entry in requests. Neither *_sessions nor *_connections has a database id. Thus, if I want to see the current database (a la sp_who2) of all sessions, to what do I join dm_exec_sessions?Thanks! |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-03 : 12:33:00
|
| maybe dm_sql_text for most_recent_sql_handle in dm_exec_connections?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
vr8ce
Starting Member
23 Posts |
Posted - 2010-12-03 : 14:36:38
|
quote:
Originally posted by nigelrivett
maybe dm_sql_text for most_recent_sql_handle in dm_exec_connections?
Thanks, but unfortunately dbid is NULL there as well. Here's the query I ran to test…select st.* from sys.dm_exec_sessions s left join sys.dm_exec_connections c on s.session_id = c.session_id cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) AS st where s.login_name = '<myvalue>' Several rows were returned, but all of them had NULL in dbid (presumably, because they're all inactive). |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-03 : 15:05:06
|
| This may be the answer to a completely different question!!, but if you are trying to "roll your own" diagnosis of sp_who2 are you familiar withAdam Machanic's "Who is Active?"That is a possible replacement for sp_who2 and includes the database name |
|
|
|
vr8ce
Starting Member
23 Posts |
Posted - 2010-12-03 : 17:45:36
|
quote:
Originally posted by Kristen
This may be the answer to a completely different question!!, but if you are trying to "roll your own" diagnosis of sp_who2 are you familiar withAdam Machanic's "Who is Active?"That is a possible replacement for sp_who2 and includes the database name
Yes, it is the answer to a completely different question. :) Who is Active uses sys.sysprocesses to get the dbid. I'm trying to figure out it if it's obtainable from just the management views. Thanks! |
|
|
|
|
|
|
|
|
Which database a session is in (mgt views)