Microsoft SQL Server: SQL Server Kill all sessions using database 

Before an existing database can be restored, there should be connections using the database in question. If the database is currently in use the RESTORE command fails with below error:

Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

To avoid this, we need to kill all sessions using the database. All sessions using the database can be queries using system stored procedure sp_who2 or using sys.dm_exec_sessionsDMV:

SP_who2 active

or

select es.session_id, sp.spid, sp.dbid,db_name(sp.dbid), object_name(dbid)from sys.dm_exec_sessions es inner join sys.sysprocesses sp 
on es.session_id = sp.spid where db_name(sp.dbid)='pControl_zfs'

or

SELECT session_id,DB_NAME(session_id) FROM     sys.dm_exec_sessions WHERE    DB_NAME(session_id) = 'pControl_zfs'
or
select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
where d.name = 'db_name'

You need to terminate each of the sessions returned individually by using KILL command.

If there are large number of sessions to kill, or you need to do this on a routine basis it gets boring to do it this way. You can *automate* this using below script, which takes database name as input, and kills all sessions connecting to it.

Method:1

DECLARE @DatabaseName nvarchar(50)
 DECLARE @SPId int
 DECLARE @SQL nvarchar(100)

 SET @DatabaseName = N'pControl_zfs_pqm'
 --SET @DatabaseName = DB_NAME()
 DECLARE my_cursor CURSOR FAST_FORWARD FOR
 SELECT SPId FROM MASTER..SysProcesses
 WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @SPId

WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @SQL = 'KILL ' + CAST(@SPId as nvarchar(10))
  print @SQL
  EXEC sp_executeSQL @SQL
  --KILL @SPId -- Causing Incorrect syntax near '@spid'.

 FETCH NEXT FROM my_cursor INTO @SPId
 END

CLOSE my_cursor
DEALLOCATE my_cursor 
go

Note: Normally will not accept the alter the database. Need to run below statement immediately after above kill session scripts.
go
ALTER DATABASE [pControl_zfs_pqm] SET MULTI_USER WITH NO_WAIT
Go
go 
ALTER DATABASE [pControl_zfs_pqm] SET SINGLE_USER WITH NO_WAIT
GO
exec sp_dboption 'db_name', 'single user', 'FALSE'
GO 
ALTER TABLE TABLENAME ALTER COLUMN COLUMNNAME varchar(100) COLLATE Latin1_General_CI_AS NULL

GO
EXEC sp_renamedb 'pControl_zfs_pqm', 'pControl_zfs_pqm_old'
use master
GO
 

Method:2

-- Kill all sessions using a database
USE [master]
GO
 
DECLARE @dbName SYSNAME
DECLARE @sqlCmd VARCHAR(MAX)
 
SET @sqlCmd = ''
SET @dbName = 'SqlAndMe'   
 -- Change database name here
SELECT   @sqlCmd = @sqlCmd + 'KILL ' + CAST(session_id AS VARCHAR) +  CHAR(13)
FROM     sys.dm_exec_sessions WHERE    DB_NAME(database_id) = @dbName
 PRINT @sqlCmd
 --Uncomment below line to kill
--EXEC (@sqlCmd)