Show connected users and disconnect them in Sqlserver through Tsql
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')
There is also a longer version of the latter here.