January 5, 2009 at 5:05 am
Does anyone have a script which kills all connections to a database where a certain sql statement is being issued. i.e. if SELECT * FROM Table1 where a= 1 exists then kill this connection?
We are having a problem with a third party app and need something like this while the supplier sorts out the issue.
January 5, 2009 at 5:13 am
dbcc inputbuffer(sp id goes here)
That will tell you the active or last statement. You can also use sp_who2 to see if it's terminated.
Then use the Kill (spid) command to terminate it.
No need to mention that you can gravely aggravate the users (if it's not the case already) by doing this. Not to mention that you can kill usefull transactions if not carefull.
January 5, 2009 at 5:29 am
Thanks for the prompt help. This is what I am doing at the moment.
Unforunately this application is creating a number of connections and then not closing them. I am then having to manually close them as you describe. However this is very timeconsuming as there can often be over 60 plus connections with the same SQL statement.
Hence I am looking for a script that Kills all the connections that are issuing a particular statement.
As I said before the issue is with the supplier, but as yet i have no definite timescale.
January 5, 2009 at 6:24 am
you could think about maybe denying select to that user on that table?
January 5, 2009 at 6:27 am
Thx for your reply. I could do but that will stop the application working which is not the intent of what I am trying to achieve. I am effectively attempting to "Clear up" after the application has run, until the application is able to do this.
January 5, 2009 at 7:00 am
Hi
You can use this script.
============
DECLARE @handle VARBINARY(64), @connection_id UNIQUEIDENTIFIER, @session_id INT
DECLARE @maxCnt INT, @cnt INT
CREATE TABLE #tmp(id INT IDENTITY(1,1), session_id INT, connection_id UNIQUEIDENTIFIER, handle VARBINARY(64))
INSERT INTO #tmp(session_id, connection_id, handle)
SELECT session_id, connection_id, most_recent_sql_handle FROM sys.dm_exec_connections
SET @maxCnt= @@IDENTITY
SET @cnt = 1
WHILE (@cnt <= @maxCnt)
BEGIN
SELECT @handle = handle, @connection_id = connection_id, @session_id = session_id FROM #tmp WHERE id = @cnt
SELECT @session_id, @connection_id, text FROM sys.dm_exec_sql_text(@handle)
SET @cnt = @cnt + 1
END
DROP TABLE #TMP
=============
Regards,
Nitin
January 5, 2009 at 7:07 am
if your application specifies it's name when connects to the database server following script may work for you
select program_name,count (*)
from sys.sysprocesses
group by program_name
order by count (*)desc
declare @spid smallint
declare appConnections insensitive cursor
for select spid
from sys.sysprocesses
where program_name='program_name'
open appConnections
fetch next from appConnections into @spid
while @@fetch_status=0
begin
print @spid
exec ('kill '+@spid)
fetch next from appConnections into @spid
end
close appConnections
deallocate appConnections
January 5, 2009 at 7:12 am
Max Yasnytskyy (1/5/2009)
if your application specifies it's name when connects to the database server following script may work for youselect program_name,count (*)
from sys.sysprocesses
group by program_name
order by count (*)desc
declare @spid smallint
declare appConnections insensitive cursor
for select spid
from sys.sysprocesses
where program_name='program_name'
open appConnections
fetch next from appConnections into @spid
while @@fetch_status=0
begin
print @spid
exec ('kill '+@spid)
fetch next from appConnections into @spid
end
close appConnections
deallocate appConnections
alternatively you can use nt_username or loginname instead of program_name in your cursor, something which will uniquely identify
that application.
January 5, 2009 at 7:22 am
Back to the basics... why is this causing you a problem? Are you having blocking issues, running out of ram?
If you just have many sleeping connections and no other side effects, I wouldn't worry to much about it. I'd maybe make a daily job that cleans this up if I get 1000s of sleeping connections, but that's about it.
January 5, 2009 at 7:48 am
Ninja's_RGR'us The SQL server instance effectively stops accepting connections to the Server. This is resolved by a stop and restart of the server.
I created a job which monitors the processes running on the server. This shows that on the ocassions this has occurred, the server was at it highest point in terms of total number of connections. One application in particular had 80+ connections that where open all with the same SQL statement.
This may seem like a stupid question, but any connections that are definied as "sleeping" have finished the execution of there SQL. (I have seen this term in SP_WHO but never understood the eact definition)
January 5, 2009 at 9:00 am
That sounds about right (I'm just no expert there), but sleeping seems like "done doing my batch" to me.
I see what you are going through. But on the other hand, if the server crashes with only 80 connections on it, you seem to have more serious issues on your hands, like an extreme lack of ram. Maybe you should do a complete check of the server's specs and see if it can perform as required.
Or you may have a memory leak. Make sure you have all the latest serviec packs installed if it's not the case already.
PLS. test on another server b4 installing on PROD!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply