There are many times when there can be a requirement to disconnect users from your Microsoft SQL database. Sometimes you can have bad code keeping connections alive that shouldn’t be, other times your simply want to restore a backup or do something that requires no users to be using the database. Reasons aside, I’ll show you some simple SQL to make it happen.

My example code if used in full will also take your database offline. The reason for this is the fact that i usually use this if i have to restore a database, and taking it offline stops more users hitting it while I’m trying to do this.

Are you sure you want to do this?

If I’m going to be doing any restorations or database admin I’m not going to do it on a live database… I think your both inexperienced AND crazy!

Some of you may be thinking that the idea behind this is all wrong. If something like this needs to be done, you should be solving the issues such as users running queries with other solutions.

Sometimes this is not possible.

Not all my clients use dedicated environments with multiple database servers and a failover cluster etc. Sometimes its sitting on a shitty shared webhost that runs many other sites off the same SQL database, and you have to make changes during off peak times in a quick and dirty fashion.

This script is for those times.

If you don’t wish to take the database offline afterwards simply remove the second part as commented below.

Enjoy:

-- use the master database
USE master
go
DECLARE @DatabaseName varchar(30), 
    @ServerProcessID varchar(10), 
    @StartTime datetime

-- Set our database name to my_database (change this to your db)
SELECT @StartTime = current_timestamp, 
    @DatabaseName = 'my_database'

-- Timeout after 3 minutes
while(exists(Select * FROM sysprocesses 
            WHERE dbid = db_id(@DatabaseName)) AND
            datediff(mi, @StartTime, current_timestamp) < 3)
begin
        DECLARE spids CURSOR FOR
            SELECT convert(varchar, spid) FROM sysprocesses
                WHERE dbid = db_id(@DatabaseName)
        OPEN spids
        while(1=1)
        BEGIN
            FETCH spids INTO @ServerProcessID
            IF @@fetch_status < 0 BREAK
            exec('kill ' + @ServerProcessID)
        END
        DEALLOCATE spids
end

-- take the database offline so that we can do our backup
IF NOT exists(Select * FROM sysprocesses 
        WHERE dbid = db_id(@DatabaseName))
            EXEC sp_dboption @DatabaseName, offline, true
else
    begin
        PRINT 
        'The following server processes are still using '+ @DatabaseName +':'
        SELECT spid, cmd, status, last_batch, 
                open_tran, program_name, hostname
        FROM sysprocesses
        WHERE dbid = db_id(@DatabaseName)
end
go