1

i was wondering if there is a way to kill a range of SQL Connections on an Microsoft SQL DB.

If i want to kill a single session id i can use kill [session-id]; go;.

If i want to kill all sessions i can switch the db mode with ALTER DATABASE [DB-NAME] SET SINGLE_USER WITHROLLBACK IMMEDIATE

But what if i want to kill session id range 60-100 for example, or all sessions from a specific Hostname or a specific username? Is there a simple way for this? I didn't found a solution with the "kill" command. Any ideas?

1
  • There is nothing built in to do what you're asking. The easiest way would be to write a query to script it for you, then copy/paste and execute. Jan 23, 2015 at 16:35

1 Answer 1

2

I wouldn't actually execute this myself. ;) However.

Declare @procid int,
        @SQL nvarchar(max)


SET @procid=60
WHILE @procid < 100
BEGIN
    set @SQL='kill ' + CONVERT(nvarchar,@procid)
    EXEC SP_EXECUTESQL @SQL
    SET @procid=@procid+1
END

As for processes from a specific hostname or username:

DECLARE @Table TABLE(
        SPID INT,
        Status VARCHAR(MAX),
        LOGIN VARCHAR(MAX),
        HostName VARCHAR(MAX),
        BlkBy VARCHAR(MAX),
        DBName VARCHAR(MAX),
        Command VARCHAR(MAX),
        CPUTime INT,
        DiskIO INT,
        LastBatch VARCHAR(MAX),
        ProgramName VARCHAR(MAX),
        SPID_1 INT,
        REQUESTID INT
)

Declare @SQL nvarchar(max),
        @spid int

INSERT INTO @Table EXEC sp_who2

SELECT  @spid = min(SPID) FROM @Table WHERE HostName/Login = 'Whatever'

while @spid is not null

BEGIN
    set @SQL='kill ' + convert(nvarchar,@spid)
    EXEC SP_EXECUTESQL @SQL
    SELECT  @spid = min(SPID) FROM @Table WHERE HostName/Login = 'Whatever' and spid > @spid            
END

(Obviously, you'd have these scripts lying around beforehand.)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .