Sometimes we need to kill all sql server transactions to perform any task.If you have to do this pls follow any one of the given steps.
1-Through management studio,when you will try to detach the db there is a option to kill all process,kill all process from here but after this click on cancel button rather than ok button.If you click on ok db will be detached.
2-Run below cmds:
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--do you stuff here
ALTER DATABASE YourDatabase SET MULTI_USER
3-Script to accomplish this, replace 'DB_NAME' with the database to kill all connections to:
USE master
GO
SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''
Set @DBName = 'DB_NAME'
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END
