Kill process error: only user processes can be killed

Question

Hi,
I run a Stored like sa/admin...and before the begin of a transaction call this SP for kill all the process.
-----------------
DECLARE @KILL_ID int
DECLARE @QUERY VARCHAR(320)

DECLARE GETEXCLUSIVE_CURSOR CURSOR FOR

--get all SPIDs from SYSOBJECTS table which match our database
SELECT
    A.SPID
FROM SYSPROCESSES A
JOIN SYSDATABASES B ON A.DBID = B.DBID
WHERE B.NAME=@DBNAME

OPEN GETEXCLUSIVE_CURSOR
    FETCH NEXT FROM GETEXCLUSIVE_CURSOR INTO @KILL_ID

    WHILE(@@FETCH_STATUS =0)
    BEGIN

        --form drop trigger query
        SET @QUERY = 'KILL '+ CONVERT(VARCHAR,@KILL_ID)

        EXEC (@QUERY)
        FETCH NEXT FROM GETEXCLUSIVE_CURSOR INTO @KILL_ID
    END

CLOSE GETEXCLUSIVE_CURSOR
DEALLOCATE GETEXCLUSIVE_CURSOR
---------------

After 5 days fine today got an error message that failure my SP:
only user processes can be killed

Now how I must kill only the real process of user and not thet one of system?
I think this the problem
TNKS Alen Italy

Answer 1

'real' user proceeses will have IDs greater than 50

 

I usually like to do it this way: Kill All Active Connections To A Database

 

ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--do you stuff here

ALTER DATABASE YourDatabase SET MULTI_USER

 

 

because while you are looping through somebody else migh connect

 

 

Denis The SQL Menace

http://www.lessthandot.com/

http://sqlservercode.blogspot.com

http://sqlblog.com/blogs/denis_gobo/default.aspx

Answer 2

Dennis, thanks for the tip! I also used to loop thru sysprocesses on previous versions of SQL Server.