February 25, 2015 at 10:03 am
Recently I needed to find all processes connected to a particular database, let's call it Test_db. I have a simple query to find all connections to my database:
select *
from sys.databases d join sys.sysprocesses p
on d.database_id = p.dbid
where d.name = 'test_db'
But there was a process that was connected to another database like USE another_db_name; but was actually selecting from tables in test_db. Is it possible to catch such connections?
Thanks
February 25, 2015 at 11:22 am
Those are cross-database queries and not technically connections between the databases. First, I'd suggest reexamining your security to prevent that type of stuff if it's occurring in ways you don't like. Next, you can use extended events to capture the queries that are using four part naming, but I think that's about the best I can offer.
"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
February 25, 2015 at 11:46 am
The problem is that we have multiple databases on same server, and sometimes developers need to run join queries between them, so we can't prohibit cross-databases queries.
About catching with extended events, I think it will be not so easy, partly because we have a lot of S.P.'s that call objects with 3-part name even within the same database.
Is there any use of new DMV's like dm_exec_requests/connections/sessions, etc ?
February 25, 2015 at 11:54 am
the question really is WHY do you need to know if any are happening, if the developers are allowed to do that. what do you want to do that is requiring exclusive access to the database? does it really matter if they had a query recently or not, if they are not running it now?
if you are just taking a database offline for restoration or something, their query would probably finish by the time the restore gets rolling.
Lowell
February 25, 2015 at 11:56 am
Not that I'm aware of, no.
"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
February 25, 2015 at 12:13 pm
If developers are allowed to do cross-database queries, then looking for where it's occurring isn't the goal. If you're concerned about applications doing it, then check the permissions of the logins used by the applications and see what they can do.
February 25, 2015 at 1:48 pm
Grant Fritchey (2/25/2015)
Those are cross-database queries and not technically connections between the databases. First, I'd suggest reexamining your security to prevent that type of stuff if it's occurring in ways you don't like. Next, you can use extended events to capture the queries that are using four part naming, but I think that's about the best I can offer.
If the code is in the database, then you shouldn't be using a 3 or 4 part naming convention because if one of the database names changes, you're going to have a huge problem to correct.
Instead, enforce the use of ONLY a 2 part naming convention and use synonyms to point to the "other" database.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 25, 2015 at 1:55 pm
Lowell (2/25/2015)
the question really is WHY do you need to know if any are happening, if the developers are allowed to do that. what do you want to do that is requiring exclusive access to the database? does it really matter if they had a query recently or not, if they are not running it now?if you are just taking a database offline for restoration or something, their query would probably finish by the time the restore gets rolling.
Yes, I needed to restore a dev database with a fresh copy from production, and as usually I checked all connections to that db from syprocesses view, because if anybody is connected I should send an email to them. But in this case, they were running some long process in another database, but also were selecting something from my target database. So from now, I decided that syspocesses is not enough and decided to find a way to find out these types of calls.
Besides, there was a case in the past when I needed to test some process before deploying to production, which in one of its steps sets a filegroup to read-only. This, in turn, requires exclusive database access. Again, I checked sysprocesses, and found that nobody besides me is connected to this database, and ran a script to set it to single user mode, thereby interrupting their process.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply