Kill sleeping SPIDs

Question

Kill sleeping SPIDs

MrXspektra

Old Hand

Points: 358
More actions
April 28, 2017 at 6:00 am

#402004

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 VARCHAR(10)
DECLARE @SQL VARCHAR(200)
DECLARE session_id_cursor CURSOR FOR
SELECT es.session_id
FROM sys.dm_exec_sessions es
WHERE
    es.last_request_start_time< DATEADD(DAY, -2, GETDATE())
    AND es.status = 'sleeping'
    and es.session_id >= 50
ORDER BY es.last_request_start_time

OPEN session_id_cursor
FETCH NEXT FROM session_id_cursorINTO @session_id

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'Kill ' + @session_id +';'
    SELECT@SQL = 'KILL ' + @session_id
    EXEC (@SQL)
      FETCHNEXT FROM session_id_cursor INTO@session_id
END
CLOSE session_id_cursor
DEALLOCATE session_id_cursor

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

deanodiergaardt - Friday, April 28, 2017 6:00 AM
Hi,
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MrXspektra Old Hand Points: 358 More actions · Answer 2

GilaMonster - Friday, April 28, 2017 6:20 AM
deanodiergaardt - Friday, April 28, 2017 6:00 AM
Hi,
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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass