How to Kill all sql server process

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

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:

You may also like...

1 Response

  1. Anonymous says:

    Great job. It worked for me.

Leave a Reply

Your email address will not be published. Required fields are marked *