using sys.dm_exec_sessions , where to find database name/id

Question

Post reply

using sys.dm_exec_sessions , where to find database name/id

winston Smith

SSCoach

Points: 19500
More actions
May 1, 2007 at 5:18 am

#195436

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?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

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

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 2

Correction: you should use db_name(dbid), not object_name

james-1023125 SSC Enthusiast Points: 183 More actions · Answer 3

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

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.

james-1023125 SSC Enthusiast Points: 183 More actions · Answer 5

Nope. sorry but you have sp. in there and no AS for sp. GTry running it yourself

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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 SSC Guru Points: 442467 More actions · Answer 7

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

james-1023125 SSC Enthusiast Points: 183 More actions · Answer 9

Yep, that works, thanks. Did not mean to offend. The G was a typo

thanks

JIM

AMtwo Grasshopper Points: 21 More actions · Answer 10

This query (joining sys.dm_exec_sessions to sys.dm_exec_requests) is functionally different from querying sys.sysprocesses.

sys.sysprocesses has the current database context for every session (regardless of whether there are any currently-executing requests).

However, joining to sys.dm_exec_requests means that you will only get results for sessions with currently-executing requests.

You'll get fewer roes using sys.dm_exec_requests, and potentially filter out valid results.

As of now, I think that the dbid data in sys.sysprocesses is uniquely available in that view, and not available in any other 2012 DMV.

sssatyan SSC Rookie Points: 25 More actions · Answer 11

sys.dm_exec_requests will show only the currently executing processes not the one waiting for connections