How can I drop all the instances attached to one Database?

Question:

How can I drop all the instances attached to one Database?

Answer:

Use KILL statement with the spid (server process IDs) to drop one instance of the database. However, this is not that easy when you have many instances. In addition, finding out spids of a given database is also a difficult process.

The following stored procedure will perform the above two processes mentioned. Create this stored procedure in master database.

CREATE PROCEDURE dbo.KillAllProcesses @dbName varchar(50)

AS

BEGIN

SET NOCOUNT ON

DECLARE @spid INT,

@cnt INT,

@sql VARCHAR(255)

SELECT @spid = MIN(spid),@cnt = COUNT(*)

FROM master..sysprocesses

WHERE dbid = DB_ID(@dbname) AND spid != @@SPID

WHILE @spid IS NOT NULL

BEGIN

SET @sql = 'KILL ' + RTRIM(@spid)

EXEC ( @sql )

SELECT @spid = MIN(spid),@cnt = COUNT(*)

FROM master..sysprocesses

WHERE dbid = DB_ID(@dbname) AND spid != @@SPID

END

END

GO

Then execute the sp by providing the database name as a parameter.

EXEC dbo.KillAllProcesses 'projects'

If you want to drop specified processes like sleeping process or suspended processes, still you can do this by modifying the below query.

SELECT @spid = MIN(spid),@cnt = COUNT(*)

FROM master..sysprocesses

WHERE dbid = DB_ID(@dbname) AND spid != @@SPID AND status = 'sleeping'