July 15, 2015 at 8:55 am
I need to find a way to identify SPIDS where queries are connected to certain databases using the 4 dot notation instead of actually being connected to the database in question.
For instance, I connect and my default database is MASTER. In MASTER, instead of typing USE MyDb ahead of my query, I just type my query as:
SELECT Col1, Col2
FROM MyDB.dbo.MyTable
The above is the simple scenario. We usually have queries connecting 3 or 4 user dbs together. So database1 might have a query pointing to database2. What I need is a way to identify the SPIDS that are connecting to database2 despite being in database1.
Google tells me to use SP_WHO and SP_WHO2. Neither works, because they are telling me what database the query is explicitly connected to, not implicitly (via the notation) connected to.
Any thoughts on how to gather this 'implicit connection' information?
July 15, 2015 at 8:58 am
Just a wild guess but maybe Adam Machanic sp_WhoIsActive can do that?
July 15, 2015 at 9:39 am
Off the top of my head, the most straightforward way to do that is probably to check all the databases in which the sessions have locks. Something like this:
SELECT request_session_id, resource_database_id
FROM sys.dm_tran_locks
GROUP BY request_session_id, resource_database_id
You can alter that as necessary to look for just the DBs for a particular spid, or all the spids for a particular DB, etc.
Cheers!
July 15, 2015 at 10:10 am
Brandie Tarvin (7/15/2015)
I need to find a way to identify SPIDS where queries are connected to certain databases using the 4 dot notation instead of actually being connected to the database in question.For instance, I connect and my default database is MASTER. In MASTER, instead of typing USE MyDb ahead of my query, I just type my query as:
SELECT Col1, Col2
FROM MyDB.dbo.MyTable
The above is the simple scenario. We usually have queries connecting 3 or 4 user dbs together. So database1 might have a query pointing to database2. What I need is a way to identify the SPIDS that are connecting to database2 despite being in database1.
Google tells me to use SP_WHO and SP_WHO2. Neither works, because they are telling me what database the query is explicitly connected to, not implicitly (via the notation) connected to.
Any thoughts on how to gather this 'implicit connection' information?
Quick suggestion
😎
USE master;
GO
SET NOCOUNT ON;
SELECT
DB_NAME(SSQLH.dbid) AS DBNAME
,*
FROM sys.dm_exec_connections DEC
LEFT OUTER JOIN sys.dm_exec_sessions DES
ON DEC.session_id = DES.session_id
LEFT OUTER JOIN sys.dm_exec_sessions DES2
on DES.security_id = DES2.original_security_id
outer apply sys.dm_exec_sql_text(DEC.most_recent_sql_handle)SSQLH;[/code]
July 15, 2015 at 10:49 am
Thanks for the quick replies, all. I will look into all solutions and let you know which one best fits my needs.
In the meantime, anyone who has other ideas, please join in the fun. The more solutions, the merrier! @=)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply