How to kill user connections in SQL server which are idle for more than 4 hours

Question

0.00/5 (No votes)

See more:

Hi all

I wrote the below code to kill the user connections in sql.

But now in this i need to add the below things.

~~1) sp_who2 should return at least 500 rows. then have to go-ahead with the rest of the part.~~

2) the idle time for the process should be more than 4 hours.

Please let me know how to add these functions in the existing..

Thank you

What I have tried:

SQL

CREATE TABLE #temp (
    spid int,
    status varchar(256),
    loginname varchar(256),
    hostname varchar(256),
    blkby varchar(256),
    dbname varchar(256),
    command varchar(256),
    cputime int,
    diskio int,
    lastbatch varchar(256),
    programName varchar(256),
    spid2 int,
    requestid int) 

INSERT INTO #temp
EXECUTE sp_who2 

select * from #temp

DECLARE kill_spids CURSOR FOR
--Add a WHERE clause here to indicate which processes to kill.
SELECT spid
FROM #temp


DECLARE @spid SMALLINT

OPEN kill_spids

FETCH NEXT FROM kill_spids INTO @spid

WHILE @@FETCH_STATUS = 0

BEGIN 

    DECLARE @dynamicsql NVARCHAR(4000)
    SET @dynamicsql = 'KILL '+CAST(@spid AS CHAR)
    PRINT @dynamicsql
    --When you are sure you know what you're doing, un-comment this line
    --EXECUTE sp_executesql @dynamicsql
    FETCH NEXT FROM kill_spids INTO @spid

END

CLOSE kill_spids
DEALLOCATE kill_spids
DROP TABLE #temp

Posted 24-Apr-16 20:58pm

Black_Rose

Updated 25-Apr-16 0:02am

v3

Add a Solution

Comments

Tomas Takac 25-Apr-16 5:28am

It sounds to me like you have no clue what "your" code actually does. Al least requirement #1 is trivial to implement as you already have all the rows returned from sp_who2 in #temp table.

Black_Rose 25-Apr-16 5:37am

1st one i identified. i need help in the 2nd one.

Tomas Takac 25-Apr-16 5:49am

There is lastbatch field but it's a varchar. I guess you'd better off using sys.sysprocesses for this.

Tomas Takac 25-Apr-16 5:50am

Also make sure you only kill sessions with spid > 50. The lower ones are reserved for system use.

Black_Rose 25-Apr-16 5:53am

i got the point of spid > 50. didn't got the earlier one. can you please give me a small example.

Tomas Takac 25-Apr-16 6:02am

Posted as solution.

Black_Rose 25-Apr-16 5:56am

I got this below sample. but here they are not using the sp_who2 command

https://gallery.technet.microsoft.com/scriptcenter/How-to-kill-SQL-Server-a33476b7
Can you please suggest me on this.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tomas Takac · Accepted Answer · 2016-04-25T00:02:00

you can achieve the same using sysprocesses system table[^] which is even easier to use.

SQL

DECLARE @sql VARCHAR(MAX) = ''
;
WITH cte AS
(
    SELECT spid
    FROM sys.sysprocesses
    WHERE spid > 50
    AND DATEDIFF(HOUR, last_batch, GETDATE()) >= 4
)
SELECT @sql = @sql + 'KILL '+CAST(spid AS VARCHAR(10)) + ';' + CHAR(13)
FROM cte

--EXEC sp_executesql @sql
PRINT @sql

This doesn't include the requirement #1 but that's just one additional IF.