Dropping a subscriber database

Usually I can simply run an ALTER statement against the database and SET SINGLE_USER WITH NO_WAIT like so:

ALTER DATABASE MyDBName SET SINGLE_USER WITH NO_WAIT

but I ran into a problem with active processes that prevented me from switching to SINGLE_USER mode. So I executed the sp_Who2 stored procedure and identified the spids responsible and killed them!

EXEC sp_Who2

I essentially ran the kill command for every spid that was associated to the specific database. So you can see it was very inefficient and a great waste of time.

KILL 54
KILL 58

Now imagine if I had to do this will 200 subscribers… not a fun time!

Then I re-ran the stored procedure sp_Who2 again to make sure they were dead.

EXEC sp_Who2

This worked for me at the time, but later in the future I don’t want to deal with the hassles of running through all the steps involved. I turned to BOL and then I remembered I could easily query the sysprocesses and mock up a script but I didn’t really have the time to run through the whole Q/A process. So I searched the interwebs and stumbled upon the blog post [Script to drop all connections to a Database] by Tijo. It was exactly what I was looking for. So I borrowed it and merged it with some additional items that are specific to our clean up steps. In a nutshell here is the final outcome.

-- KILL ACTIVE PROCESSES
DECLARE @db NVARCHAR(50)
DECLARE @spid int
SET @db = 'myDBName'

SELECT @spid = MIN(spid) 
FROM sys.sysprocesses
WHERE dbid = db_id(@db)
WHILE @spid IS NOT NULL
	BEGIN
		SELECT @spid
		EXEC ('KILL ' + @spid)
		SELECT @spid = MIN(spid) 
		FROM sys.sysprocesses
		WHERE dbid = db_id(@db)
	END
	
-- DROP DATABASE
DECLARE @cmd1 NVARCHAR(75)
DECLARE @cmd2 NVARCHAR(50)
SET @cmd1 = 'ALTER DATABASE ' + @db + ' SET SINGLE_USER WITH NO_WAIT'
SET @cmd2 = 'DROP DATABASE ' + @db
EXEC(@cmd1)
EXEC(@cmd2)
	
-- DROP USER ACCOUNT
DECLARE @cmd3 NVARCHAR(65)
IF  EXISTS (SELECT * FROM sys.syslogins WHERE name = + HOST_NAME() + '\MergeAgent')
SET @cmd3 = 'DROP LOGIN [' + HOST_NAME() + '\MergeAgent]'
EXEC(@cmd3)
GO

Also thinking about the future I combined this sql script with a batch file using SQLCMD and provided it to the onsite support staff so that they can handle the reinitialization for subscriptions that fall outside of their threshold.

@echo off
color 17
Title Device Reinit Clean Up . . .

:: =========================
SET srv=(local)\Instance
:: =========================

SQLCMD -S %srv% -E -i "Device.ReinitCleanUp.sql"
PAUSE

Both files need to reside in the same directory in order to work; however, if you decide to call the sql script from a network share be sure to make the necessary adjustments. Reason being is that we don’t deploy management studio on the subscribers and some of the onsite technical support staff are unfamiliar with SQL Server. So I would rather not take any chances which is why I am going to add an additional condition.

IF HOST_NAME() = 'servername'
BEGIN
	PRINT '**** WARNING ****'
	PRINT 'You attempted to apply the cleanup script against the Production Server!'
	PRINT 'Terminating process!'
END

ELSE
	BEGIN
		-- KILL ACTIVE PROCESSES
		DECLARE @db NVARCHAR(50)
		DECLARE @spid int
		SET @db = 'myDBName'

		SELECT @spid = MIN(spid) 
		FROM sys.sysprocesses
		WHERE dbid = db_id(@db)
		WHILE @spid IS NOT NULL
			BEGIN
				SELECT @spid
				EXEC ('KILL ' + @spid)
				SELECT @spid = MIN(spid) 
				FROM sys.sysprocesses
				WHERE dbid = db_id(@db)
			END
			
		-- DROP DATABASE
		DECLARE @cmd1 NVARCHAR(75)
		DECLARE @cmd2 NVARCHAR(50)
		SET @cmd1 = 'ALTER DATABASE ' + @db + ' SET SINGLE_USER WITH NO_WAIT'
		SET @cmd2 = 'DROP DATABASE ' + @db
		EXEC(@cmd1)
		EXEC(@cmd2)
			
		-- DROP USER ACCOUNT
		DECLARE @cmd3 NVARCHAR(65)
		IF  EXISTS (SELECT * FROM sys.syslogins WHERE name = + HOST_NAME() + '\MergeAgent')
		SET @cmd3 = 'DROP LOGIN [' + HOST_NAME() + '\MergeAgent]'
		EXEC(@cmd3)
	END