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’
]]>