SQLServerCentral / SQL Server 2005 / SQL Server 2005 General Discussion / using sys.dm_exec_sessions , where to find database name/id / Latest Posts

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

andy.mallon+SQL — Mon, 07 Jan 2013 11:42:47 GMT

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.

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

james-1023125 — Fri, 22 Jun 2012 07:46:50 GMT

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

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

SQLRNNR — Thu, 21 Jun 2012 15:49:39 GMT

This should do it[code="sql"]select es.session_id, er.database_id, db_name(er.database_id) AS DBNamefrom sys.dm_exec_sessions es inner join sys.dm_exec_requests er on es.session_id = er.session_id;[/code]

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

Lynn Pettis — Thu, 21 Jun 2012 15:49:31 GMT

[quote][b]james-1023125 (6/21/2012)[/b][hr]Nope. sorry but you have sp. in there and no AS for sp. GTry running it yourself[/quote]Also, didn't see you suggest any alternatives, so keep your snarkiness to yourself please.

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

Lynn Pettis — Thu, 21 Jun 2012 15:47:00 GMT

[quote][b]james-1023125 (6/21/2012)[/b][hr]Nope. sorry but you have sp. in there and no AS for sp. GTry running it yourself[/quote]Yep, I missed changing a couple of sp aliases. Sorry.

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

james-1023125 — Thu, 21 Jun 2012 15:40:07 GMT

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

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

Lynn Pettis — Thu, 21 Jun 2012 14:51:02 GMT

How about this:[code="sql"]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;[/code]Edit: Corrected a couple of aliases.

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

james-1023125 — Thu, 21 Jun 2012 14:43:50 GMT

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_ ... viewsread here http://msdn.microsoft.com/en-us/library/ms179881.aspxJIM

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

Mark Shvarts 1 — Tue, 01 May 2007 11:24:00 GMT

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

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

lucky-80472 — Tue, 01 May 2007 07:49:00 GMT

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

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

winston Smith — Tue, 01 May 2007 05:18:00 GMT

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?