Needing a change… Modify your Filename

A friend of mine told me that he felt a need for a change. Without question I said cool. Change is always inviting as it keeps things interesting. Little did I know or realize he was referring to the changing of his database file name. So he issued the simple ALTER DATABASE MODIFY FILE statement and specified a new name for his database.

ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks_data.mdf')
ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_Log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks_log.ldf')

and in the results were

The file "AdventureWorks_data" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "AdventureWorks_Log" has been modified in the system catalog. The new path will be used the next time the database is started.

So he took this message as needing to restart the SQL Server Engine Service. Which he did; however, that did not satisfy the underlying issue. After refreshing the databases he attempted to expand the db and received a nice little error indicating that the database was not accessible. Which is when I received the call.

Having experienced this myself I remembered that:

1. the database needed to be taken offline

ALTER DATABASE AdventureWorks SET OFFLINE WITH ROLLBACK IMMEDIATE

2. he would need to physically rename the files accordingly

3. bring the database back online

ALTER DATABASE AdventureWorks SET ONLINE WITH ROLLBACK IMMEDIATE

Once these steps were completed he was back in business. On a side note this permitted the changing of the file names but you can also change the actual file path location for each of the files as well using the same syntax. If you wanted to update the logical name for both the data and log file below is the syntax for doing so.

ALTER DATABASE [AdventureWorks] MODIFY FILE (NAME=N'AdWks', NEWNAME=N'AdventureWorks_data')
ALTER DATABASE [AdventureWorks] MODIFY FILE (NAME=N'AdWks_log', NEWNAME=N'AdventureWorks_log')

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