Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

using sys.dm_exec_sessions , where to find database name/id Expand / Collapse
Author
Message
Posted Tuesday, May 01, 2007 5:18 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:57 AM
Points: 875, Visits: 1,760
im using sys.dm_exec_sessions to show me all current sessions ( instead of using sp_who2). the problem is that sys.dm_exec_sessions doesnt have the database name or id. where can i map the spid to , to get the db name?
Post #362235
Posted Tuesday, May 01, 2007 7:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, January 10, 2014 3:03 PM
Points: 1,295, Visits: 752

Hello Smith,

Can you check whether this query gives out the desired results you want?

select es.session_id, sp.spid, sp.dbid, object_name(dbid)

from sys.dm_exec_sessions es inner join sys.sysprocesses sp on es.session_id = sp.spid

Thanks

 




Lucky
Post #362279
Posted Tuesday, May 01, 2007 11:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 02, 2013 9:22 AM
Points: 386, Visits: 441
Correction: you should use db_name(dbid), not object_name
Post #362385
Posted Thursday, June 21, 2012 2:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 3:23 PM
Points: 3, Visits: 5
But the question was, and is how to get it from sys.dm_exec_sessions. You are joining to the sysprocesses which in deprecated by MS SQL Server 2012 to the sys.dm_ ... views

read here http://msdn.microsoft.com/en-us/library/ms179881.aspx

JIM
Post #1319654
Posted Thursday, June 21, 2012 2:51 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 22,479, Visits: 30,162
How about this:


select
es.session_id,
er.session_id,
er.dbid,
db_name(er.dbid)
from
sys.dm_exec_sessions es
inner join sys.dm_exec_requests er on es.session_id = er.session_id;


Edit: Corrected a couple of aliases.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1319657
Posted Thursday, June 21, 2012 3:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 3:23 PM
Points: 3, Visits: 5
Nope. sorry but you have sp. in there and no AS for sp. GTry running it yourself
Post #1319693
Posted Thursday, June 21, 2012 3:47 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 22,479, Visits: 30,162
james-1023125 (6/21/2012)
Nope. sorry but you have sp. in there and no AS for sp. GTry running it yourself


Yep, I missed changing a couple of sp aliases. Sorry.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1319698
Posted Thursday, June 21, 2012 3:49 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 22,479, Visits: 30,162
james-1023125 (6/21/2012)
Nope. sorry but you have sp. in there and no AS for sp. GTry running it yourself


Also, didn't see you suggest any alternatives, so keep your snarkiness to yourself please.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1319699
Posted Thursday, June 21, 2012 3:49 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:11 PM
Points: 20,455, Visits: 14,075
This should do it

select
es.session_id,
er.database_id,
db_name(er.database_id) AS DBName
from
sys.dm_exec_sessions es
inner join sys.dm_exec_requests er on es.session_id = er.session_id;





Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1319700
Posted Friday, June 22, 2012 7:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 3:23 PM
Points: 3, Visits: 5
Yep, that works, thanks. Did not mean to offend. The G was a typo

thanks

JIM
Post #1319945
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse