Sometimes, we need to take a SQL Server database offline. After notifying the users and giving them plenty of warning, they leave connections to the database – some connections are SLEEPING or AWAITING COMMAND, others have select statements that have been running for over four hours. I use the following sql to kill off all of the remaining user sessions on a database. Just change the YOURDATABASE text to the name of your database and generate a script to kill off all connections to that database. If you prefer to just kill off the connections, uncomment the exec(@sqlstring) line and the connections will be killed. Act quickly though. Some web servers will try to reconnect to the database even if there are no users requesting data.
DECLARE @max_count INT, @count INT, @sqlstring VARCHAR(100); DECLARE @spid_table TABLE(spid INT NOT NULL); INSERT INTO @spid_table SELECT spid FROM sys.sysprocesses WHERE dbid = DB_ID(N'YOURDATABASE'); SELECT @max_count = MAX(spid) FROM @spid_table; SELECT TOP 1 @count = spid FROM @spid_table; WHILE @count <= @max_count BEGIN SELECT @sqlstring = 'kill '+CONVERT(VARCHAR(4), @count); --exec(@sqlstring) PRINT @sqlstring; IF @count = @max_count BEGIN BREAK; END; ELSE BEGIN SELECT TOP 1 @count = spid FROM @spid_table WHERE spid > @count; END; END;