Today I was trying to restore a backup of our MSSQL server when a user decided to drive me crazy by not disconnecting after telling anyone that we need to do that restore. So I fired up the management studio and had a look at the current processes by using the following query:

select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses

As I wasn’t in the mood to argue with users today and begging them for cooperation, I decided to do it the uncomplicated way by killing some processes. Identifying the user would be unneeded, as that guy would call the support hotline anyways, ending up at my desk.

A SQL script looks up the processes accessing the database I am about to restore and kills those:

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

And finally the restore worked without problems. Talking about timing, the user who wouldn't want to disconnect called me. I picked up the phone and said with a voice as sweet as possible "Simon, Operator speaking?"...