Kill all the sessions of a database at one time

I was working with my one of junior database administrator. He was working on one of the restore in stage environment. Request was to restore stage from latest backup of prod so that build can be tested before moving to prod.

When he tried restoring the database, he got the error ‘Database is in use’ and he started execution sp_who2 ‘Active’ and started killing the sessions one by one. After killing all the sessions, he tried database restore again and it failed. Again he repeated the same process to kill the sessions one by one. He did it but it was pain for him again and again doing this.

Below is the script I have come up with which can be used to kill all the sessions at one time. Just change the database name and execute it. It will kill all the sessions at one time and you will be able to run your restore with immediate effect.

USE master

GO

SET NOCOUNT ON;

IF EXISTS(SELECT * FROM sys.databases WHERE name = '<DB_Name>')

      BEGIN

      DECLARE     @spid int,

                  @sql nvarchar(max) ;

      DECLARE curSpid CURSOR FOR

      SELECT spid FROM sys.sysprocesses WHERE db_name(dbid) = '<DB_Name>' ;

      OPEN curSpid ;

      FETCH NEXT FROM curSpid INTO @spid ;

      IF @@FETCH_STATUS = 0

            BEGIN

            PRINT 'Killing open connections to database <DB_Name>.';

            END

      WHILE @@FETCH_STATUS = 0

            BEGIN      

            SET @sql = N'KILL ' + CAST(@spid AS nvarchar(32)) + N';'

            EXEC sp_executesql @sql;

            FETCH NEXT FROM curSpid INTO @spid ;

            END

      END

GO

close curSpid

deallocate  curSpid

go

Hope this helps to all of my friends.