Who's connected to my MSSQL server?

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?”…