SQL Server: Kill All the SPIDs

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;