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

Update your Skill-Set and Stay Marketable in IT

It’s no secret that you need to stay on top of the latest technologies in order to stay marketable, but how do you do it on a tight budget? For one you cannot just go out and purchase the most beefy server(s) available because the hardware alone can cost at minimum two-three months rent and that’s a scaled down version. The server’s I often dream about are way out of my budgetary league. Then you have the software and licensing to add to the piling list of expenses. So where do you turn? How about virtualization? After all virtualization is booming so while you’re practicing Active Directory management or SQL Server Mirroring you can learn a few things about virtualization in the process. And that my friend is what I consider a true win-win scenario!

Virtualizing as I like to call it is essentially running or hosting a guest operating system on top of another system. Your current system should have at least 4GB of RAM and running at least an Intel Core2Duo processor or comparable AMD. I mean you could make do with less but performance will suffer. Don’t get me wrong virtualization can be costly but there are affordable versions available. I am bias to VMware and I love Workstation 7 but it comes with a price. However VMware Server is (FREE), as well as VirtualBox (FREE) by SUN and VirtualPC (FREE) by Microsoft to name a few. I have used all of the software listed and found that VMware has the fastest load times. I am talking about installing and loading the Operating System form an ISO CD image. Honestly in the end they all accomplished the same results so no matter what you choose you will still reap benefits of virtualization. Now you just need to get your hands on some operating systems and server software.

Good news… if you are a college student you have some options. Academic versions are often offered at significantly reduced prices. If that is not an option you can sign up for Dreamspark.com and get software for free, but you need an active college email address to validate your student status. At the very least you have the trials editions offered by Microsoft that are readily available but you will be limited to 120 days. You can always reseal the OS and extend your trial a bit further so keep that in mind. With either software option you should get enough experience and understanding with the OS to hold an intelligent conversation about the subject matter.

Some people tend to think that their company should invest in them and pay for technical training when in reality you really need to invest in yourself. It speaks volumes about a person who is able to tackle new functionality from new releases let alone get certified without the aided help of the employer. Honestly how else are you supposed to get ahead and advance your career without putting forth the effort to learn on your own? Trust me education through osmosis does not work so don’t just by a book hoping it will make you smarter. You have to open it and read the pages to get the most out of that investment. Not to mention picking up some reading material is always a good idea and it’s also a great way to work towards certification. If all else fails you can always turn to google or bing and they are both filled with tons of useful resources.

If at all you are into SQL Server you can download SQL Server 2008 R2 Express which will provide you with the tools necessary to perform the install, configurations including settings and security, restores, backups, business intelligence and well as diving into the Transact-SQL query language. However if you want to work with mirroring, replication, high availability then you can opt for Developer Edition which is typically $50. It provides all the functionality of Enterprise edition with limitations of course. So you can create database snapshots, setup principal and mirror servers, setup a publication, remote or local distributors and other good stuff. So what are you waiting for? It’s at your grasp… so grab a hold of it and run with it!