April 28, 2017 at 6:00 am
Hi,
I've written a query to loop through a all the sleeping SPIDs and kill it sequentially. However it is not working any i'm getting the following error:
KILL command cannot be used inside user transactions.
Please advise?
DECLARE @session_id
DECLARE @SQL
DECLARE session_id_cursor
SELECT es
FROM sys
WHERE
es
AND
and
ORDER BY
OPEN session_id_cursor
FETCH NEXT
WHILE @@FETCH_STATUS
BEGIN
SET @SQL
SELECT@SQL =
EXEC
FETCHNEXT FROM
END
CLOSE session_id_cursor
DEALLOCATE session_id_cursor
April 28, 2017 at 6:20 am
deanodiergaardt - Friday, April 28, 2017 6:00 AMHi,I've written a query to loop through a all the sleeping SPIDs and kill it sequentially.
Why?
Sleeping connections aren't doing any harm. Other than a couple MB memory for their stacks, they take no resources. Killing them, however, may cause problems if the application isn't written to handle broken connections well.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2017 at 6:28 am
GilaMonster - Friday, April 28, 2017 6:20 AMdeanodiergaardt - Friday, April 28, 2017 6:00 AMHi,I've written a query to loop through a all the sleeping SPIDs and kill it sequentially.
Why?
Sleeping connections aren't doing any harm. Other than a couple MB memory for their stacks, they take no resources. Killing them, however, may cause problems if the application isn't written to handle broken connections well.
This is to test functionality for the application and its ability to handle the broken connections.
April 28, 2017 at 6:38 am
Um..., ok. If you're testing that, you probably don't want the filter to only retrieve sessions that haven't done anything for two days, or it's going to be a very long test. In fact it'll probably be easier to just manually kill a session that you know the app is using for the test, no need for complex code.
The error message tells you the exact problem.
"KILL command cannot be used inside user transactions"
You've got a transaction open and you're trying to run KILL from inside it, and that's not permitted.
Oh, and the filter for session_id>50 is also wrong. I assume you're trying to filter out system sessions, but the 'rule' that all system sessions have a session_id under 50 hasn't been true since SQL Server 2000.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply