June 8, 2015 at 6:01 am
Hi, the old way of obtaining database context from a session id (e.g. the code inside sp_who2) was to use something like
SELECT dbid FROM master..sysprocesses WHERE spid = @@SPID
Like many undocumented features it does not seem to have been updated to use the newer DMVs.
I can't find a way to do the same thing with a DMV. Does anyone know how to do this?
June 8, 2015 at 6:13 am
graham.day 53003 (6/8/2015)
Hi, the old way of obtaining database context from a session id (e.g. the code inside sp_who2) was to use something likeSELECT dbid FROM master..sysprocesses WHERE spid = @@SPID
Like many undocumented features it does not seem to have been updated to use the newer DMVs.
I can't find a way to do the same thing with a DMV. Does anyone know how to do this?
Hi and welcome to the forums.
The DMVs use the session_id column to expose the SPID. So you could use the following:
select * from sys.dm_exec_sessions where session_id = @@SPID;
June 8, 2015 at 6:30 am
Ed Wagner (6/8/2015)
graham.day 53003 (6/8/2015)
Hi, the old way of obtaining database context from a session id (e.g. the code inside sp_who2) was to use something likeSELECT dbid FROM master..sysprocesses WHERE spid = @@SPID
Like many undocumented features it does not seem to have been updated to use the newer DMVs.
I can't find a way to do the same thing with a DMV. Does anyone know how to do this?
Hi and welcome to the forums.
The DMVs use the session_id column to expose the SPID. So you could use the following:
select * from sys.dm_exec_sessions where session_id = @@SPID;
Thank you for your reply. Please forgive me, it is not clear to me how you propose to derive the database id from @@SPID using sys.dm_exec_sessions? Would you join it on another view?
June 8, 2015 at 6:40 am
Assuming it's an active query, sys.dm_exec_requests has the database id right in the results. You could join that to sysdatabases to get the database name.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 8, 2015 at 6:42 am
My apologies. I thought your intent was asking what column had replaced spid in the DMVs.
You can look at sys.dm_exec_requests and filter on session_id. It contains the database_id.
June 8, 2015 at 6:53 am
Thank you for your replies.
What I am trying to do with this is to identify databases which are never connected to by means of auditing user connections. (Logon Triggers are useless for this as they execute on authentication complete, and before database connection, so DB_ID() always returns 1).
I can use the old sysprocesses system view to do this, but I am trying to find a way to rewrite it using only current DMVs.
I appreciate the point that database id is present on sys.dm_exec_requests, but I can't guarantee that user connections will run queries.
Perhaps there simply is no way to determine the database context for all user connections using DMVs?
June 8, 2015 at 7:07 am
To determine connections, and just that, I'd probably use extended events. There's a login event that will tell you that. Now, understand, if people login to one database and then change context to another, or query to another, that login event won't help. Then, you'd just need to capture all the RPC and batch completes. That will of course tell you all references to a database (although again, cross database queries won't show up unless you query for them)
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 8, 2015 at 7:12 am
Thanks Grant, that sounds a very comprehensive workaround. I'll take a look at this approach.
June 8, 2015 at 7:17 am
The first thing I would check is whether the logins enabled for the database are actually logging in or not. You can captur this event with Extended Events, as Grant suggested.
If you want to track the usage of the database in terms of reads/writes to tables, you can rely on index usage stats, as described in this post: http://spaghettidba.com/2015/04/20/tracking-table-usage-and-identifying-unused-objects/
Hope this helps
-- Gianluca Sartori
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply