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