The Need

I’ve found myself, more times than once, needing to change disk drives that my user databases are found on. This could come up because you are moving a server and need to switch SANs, migrate from local storage to SAN storage, or need to move to a new LUN because it was over expanded and your SAN techs can’t shrink it without a drop/create.

This exercise can be done with the GUI or with typed T-SQL but in my cases there are usually a couple of dozen databases with file in a number of different sub-directories. Today I’m going to go over each method but conclude on a T-SQL script that I wrote to do almost all of the work for us.

Method #1: T-SQL

To detach a database and re-attach you will be using two different types of T-SQL. For the detach method you will be using the stored procedure sp_detach_db while for the attachment you will be using a CREATE DATABASE command with the FOR ATTACH option.

The syntax for the detachment on SQL Server 7.0 through 2012:

USE master;
GO
EXEC sp_detach_db @dbname = N'AdventureWorks2012';
GO

Syntax for the attachment on SQL Server 2005 through 2012:

USE master;
GO
CREATE DATABASE MyAdventureWorks 
    ON (FILENAME = 'C:\MySQLServer\AdventureWorks2012_Data.mdf'),
    (FILENAME = 'C:\MySQLServer\AdventureWorks2012_Log.ldf')
    FOR ATTACH;
GO

Syntax for the attachment on SQL Server 7.0 and 2000:

use master
go
sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'
go

Method #2: GUI via SSMS

1. Detach
   1. Right click the database that you wish to detach and select Tasks->detach.
   2. 
   3. Check “Drop Connections” so that your detach won’t be prone to timing out due to a persisted connection.
   4. 
   5. Click OK.
2. Attach
   1. Right click Databases and select Attach…
   2. 
   3. When the Attach Databases window opens, click Add…
   4. 
   5. Locate and select the mdf file for your database and click OK.
   6. 
   7. When your mdf file is selected the “database details” group box will be populated with a list of all of the files that are associated to the mdf. In my screenshot below the log file was discovered because it was in the same directory. If your ldf or ndf files are not in that same location there will be text in the “Message” field that will indicate that you need to tell it where the file is. You would, in this case, click the ellipsis that I have highlighted and navigate to the file in question.
   8. 
   9. Click OK and your databases will be attached.

Method #3: T-SQL Script Generation

Below is the script I wrote to write all of my detach and attach statements for me. It first prints out the detach statements but it queries for the database file locations and builds your attachment statements as well.

Important: Once you run the detach scripts you will no longer be able to query the file information so don’t make the mistake of running these in the same query window as the print statements.

DECLARE @name VARCHAR(50)
DECLARE @path VARCHAR(MAX)
DECLARE @execSQL VARCHAR(MAX)
DECLARE @filename VARCHAR(MAX)
DECLARE @database_ID INT

--Place the directory paths here
DECLARE @newdatapath VARCHAR(MAX) = 'Q:\InstanceName\'
DECLARE @newlogpath VARCHAR(MAX) = 'Z:\InstanceName\'

--populate file locations
SELECT name, physical_name AS CurrentLocation, database_id
INTO #temptable
FROM sys.master_files
WHERE [database_id] > 4

--detach
DECLARE detach_cursor CURSOR FAST_FORWARD FOR
SELECT name, physical_name AS CurrentLocation, database_id
FROM sys.master_files
WHERE file_id = 1
AND name <> 'master'
AND name <> 'tempdev'
AND name <> 'msdbdata'
AND name <> 'modeldev'

OPEN detach_cursor
FETCH NEXT FROM detach_cursor INTO @name, @path, @database_ID

PRINT  'USE master;' + CHAR(13) + CHAR(10)

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @execSQL = 'EXEC sp_detach_db @dbname = N''' + DB_NAME(@database_ID) + ''';'
	PRINT @execSQL

	FETCH NEXT FROM detach_cursor INTO @name, @path, @database_ID
END
CLOSE detach_cursor
DEALLOCATE detach_cursor

--attach
PRINT CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

DECLARE attach_db_cursor CURSOR FAST_FORWARD
FOR
        SELECT DISTINCT [database_id]
        FROM    #temptable

OPEN attach_db_cursor
FETCH NEXT FROM attach_db_cursor INTO @database_ID

PRINT 'USE master;' + CHAR(13) + CHAR(10)

WHILE ( SELECT  fetch_status
        FROM    sys.dm_exec_cursors(@@SPID)
        WHERE   name = 'attach_db_cursor'
      ) = 0 
      BEGIN

			SET @execSQL = 'CREATE DATABASE ' + DB_NAME(@database_ID) + ' ON' + CHAR(13) + CHAR(10)

            DECLARE attach_file_cursor CURSOR FAST_FORWARD
            FOR
                    SELECT  [name]
                          , [CurrentLocation]
                          , [database_id]
                    FROM    #temptable
                    WHERE [database_id] = @database_ID

            OPEN attach_file_cursor
            FETCH NEXT FROM attach_file_cursor INTO @name, @path, @database_ID

            WHILE ( SELECT  fetch_status
                    FROM    sys.dm_exec_cursors(@@SPID)
                    WHERE   name = 'attach_file_cursor'
                  ) = 0 
                  BEGIN
                        SET @filename = @path
                        WHILE CHARINDEX('\', @filename, 0) <> 0 
                              BEGIN
                                    SET @filename = RIGHT(@filename,LEN(@filename) - CHARINDEX('\',@filename, 0))
                              END

						IF RIGHT(@filename,3) = 'ldf'
						BEGIN
							SET @execSQL = @execSQL + '(FILENAME = ''' + @newlogpath + @filename + '''),' + CHAR(13) + CHAR(10)
						END
						ELSE
						BEGIN
							SET @execSQL = @execSQL + '(FILENAME = ''' + @newdatapath + @filename + '''),' + CHAR(13) + CHAR(10)
						END

                        FETCH NEXT FROM attach_file_cursor INTO @name, @path,
                              @database_ID
                  END

                  SET @execSQL = LEFT(@execSQL,LEN(@execSQL)-3) + CHAR(13) + CHAR(10)
                  SET @execSQL = @execSQL + ' FOR ATTACH;'

                  CLOSE attach_file_cursor
				  DEALLOCATE attach_file_cursor

				  PRINT @execSQL

             FETCH NEXT FROM attach_db_cursor INTO @database_ID
      END

CLOSE attach_db_cursor
DEALLOCATE attach_db_cursor

DROP TABLE #temptable

I currently have this script setup to accept a data and log path in the variables @newdatapath and @newlogpath at the top of the script. This is for if you know the directories that you would like to move the databases to and if they are all the same for each database. I find this situation common but I have come across situations where I wanted to move drives but not change the folder structure. In that case I would build the folder structure and then replace the below lines of the script.

--Before
IF RIGHT(@filename,3) = 'ldf'
BEGIN
	SET @execSQL = @execSQL + '(FILENAME = ''' + @newlogpath + @filename + '''),' + CHAR(13) + CHAR(10)
END
ELSE
BEGIN
	SET @execSQL = @execSQL + '(FILENAME = ''' + @newdatapath + @filename + '''),' + CHAR(13) + CHAR(10)
END

--With change
IF RIGHT(@filename,3) = 'ldf'
BEGIN
	SET @execSQL = @execSQL + '(FILENAME = ''' + REPLACE(REPLACE(@path,'E:\','Q:\'),'F:\','Z:\') + '''),' + CHAR(13) + CHAR(10)
END
ELSE
BEGIN
	SET @execSQL = @execSQL + '(FILENAME = ''' + REPLACE(REPLACE(@path,'E:\','Q:\'),'F:\','Z:\') + '''),' + CHAR(13) + CHAR(10)
END

Success

Using method #3 I was able to relocate the database files on 2 servers with 5 instances each and 1-2 dozen databases per instance in less than 10 minutes.

Now I just need to move the system databases.

The Problem:

Today I noticed the data drive of one of my Production servers growing. It wasn’t growing overly fast and it had been quite some time since the last SAN drive expansion so I gave the thumbs up to my SAN techs to add 5GBs of space. I started with 5GBs just out of a hunch even though I knew in a couple weeks I’d be giving the green light to add more space again.

It turned out that adding 5GBs at first was a good idea because what I assumed with normal data growth turned out to be a ballooning distribution database. When I noticed my disk space alert again in the same day is when I got wise because this server should not grow 5 GBs in a day.

After a quick look it was easy to notice the distribution database was the issue with it being 48GBs which is far above normal for the publications that it handles.

The Cause:

I went ahead and pulled up my friend, the replication monitor, and noticed a red X over the publisher with the ballooned distribution database. Upon further investigation I noticed 5 publications were red and the offending subscription in each was for the same subscriber (our source database for our data warehouse which is used to perform warehouse tuning operations separate from the transactional data).

I pulled up the subscriber and noticed that it has no subscriptions. What apparently happened was, one of my DBAs had done a restore without using the KEEP_REPLICATION flag. This restored the database who was a subscriber but broke the transfer. Since the source still had the subscription registered in it all the transactions for the last 72 hours were being held and not purged because the default retention period for a distribution database is 72 hours.

The Fix:

So what I ended up doing was first dropping all of the subscriptions that were broken and then recreating them. This fixed the root cause but my distribution database was still large and had 0% free space to shrink.

What I had to do next was hit up the Google. I knew that the job ‘Distribution clean up: distribution’ was running and would eventually clean up my database but when I have a problem I like to stick to it until fixed and this job was running slowly. That is when I found clsilva’s post Replication: Distribution Cleanup and Paul Ibison’s post How to Massively Optimise Transactional Replication.

The command that clsilva talks about, EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72, is actually exactly what the job, Distribution clean up: distribution is running that is slow.

This process is slow because it is trying to purge a row count, which in my case, is very large. I ended up stopping this job and running the commands myself in steps as recommended by clsilva. I started with a 2 hour block and found that I was deleting records at a 2 row per second rate. This was sufficient evidence that to me that the process was running successfully but was simply very slow. When you have a working system stored procedure I never dive down deeper and try to tweak things myself. So what I ended doing was executed a script that ran this command decrementing the max and min values by 10 hours a piece in separate batches. The only purpose of this was so that I could have a 10 hour incremented print out in SSMS that would reassure me and give me a pseudo-progress bar.

The End

Upon completion of my script I shrunk my distribution database down to its normal size and went on with my day.