Selfelected

The title says it all.

For example when one restores a database one has to disconnect all users.  Here is a script for that.

USE master
GO
DECLARE @dbname varchar(30), @spid varchar(10), @start datetime
SELECT @start = current_timestamp, @dbname = 'dbname'
-- Timeout after 5 mts
while(exists(Select * FROM sysprocesses WHERE dbid = db_id(@dbname)) AND
 datediff(mi, @start, current_timestamp) < 5)
    begin
     DECLARE spids CURSOR FOR
      SELECT convert(varchar, spid) FROM sysprocesses
       WHERE dbid = db_id(@dbname)
     OPEN spids
     while(1=1)
     BEGIN
      FETCH spids INTO @spid
      IF @@fetch_status < 0 BREAK
      exec('kill ' + @spid)
     END
     DEALLOCATE spids
END
GO

I recently forgot to close the connection and looped through records so the connection pool was exhausted and people couldn’t log on to the system.  Typically something one finds out in a live system where many use your web at once.  At least that was the case for me.  Below is the code I ran over and over again while looping to make sure my updated code didn’t use too many connections in the pool.

USE MASTER
SELECT * FROM sysprocesses WHERE dbid = DB_ID('dbname')

Honor those who should.

There is also a longer version of the latter here.

Tags: sqlserver