Tuesday, April 21, 2009

MS SQL Server: Disconnect Users From Database with KILL SID (Kill User Session)

You can delete all the active session with the following query.


DECLARE @spid INT
DECLARE @query NVARCHAR(255)

DECLARE processes CURSOR FOR
SELECT spid FROM master..sysprocesses
WHERE dbid = DB_ID('Your_Database_Name')
AND spid != @@SPID
OPEN processes
FETCH NEXT FROM processes
INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'KILLING '+RTRIM(@spid)
SET @query = 'KILL '+RTRIM(@spid)
EXEC(@query)
FETCH NEXT FROM processes
INTO @spid
END
CLOSE processes
DEALLOCATE processes

No comments: