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.
-- 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