Disconnect all users from database

This is to reply Mohit's comment on my yesterday's post "Forcefully Disconnect All Users and Drop a Database" . I thought it would be nice to have it as new post rather than just a comment.

He had mentioned one scenario where there are times when you just have to disconnect other users from the database. We can acheive it by just killing all the processes connected to that particular database. I have written small stored procedure to do this:

CREATE PROCEDURE dbo.removeConnections
@database_name SYSNAME
AS
BEGIN
DECLARE @spid INT
DECLARE @query NVARCHAR(255)

DECLARE processes CURSOR FOR
SELECT spid FROM master..sysprocesses
WHERE dbid = DB_ID(@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
END
GO

~ Kiran

Forcefully Disconnect All Users and Drop a Database

Yesterday, one of my colleague was telling me that he was not able to delete the database. When I checked the issue at his desk, I saw that other users were connected to that database without his knowledge and hence he was getting error while deleting it.

One way to get rid of it immediately:

ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE MyDatabase

~ Kiran