Moving database files for a replicated SQL Server database

By: Mohammed Moinudheen | Comments (9) | Related: 1 | 2 | 3 | 4 | 5 | More > Replication

Problem

I am running out of disk space for one of my SQL Server replicated databases. I would like to move the database files of my publication database, which is part of transactional replication, to a drive on the same server, which is has more free space. Which is the better option for moving the files without impacting the transactional replication: sp_attach_db/sp_detach_db or alter database modify file? Check out this tip for the solution.

Solution

Running out of disk space is an all too common issue. Databases grow over time and we need to move them to support the business. In this tip will review two options (sp_attach_db/sp_detach_db and alter database modify file) to see which one makes most sense.

Option 1 - sp_detach_db

The sp_detach_db option is a deprecated feature and will not be available in future SQL Server versions. However, we can try this command to see what happens. When we try this option on a replication publication database we get the following error message as shown below:

Msg 3724, Level 16, State 1, Line 1 Cannot drop the database '%database name%' because it is being used for replication.

running out of disk space on a sql server replicated database and moving them to another database

In order to perform the detach operation we need to disable the publishing option using the sp_replicationdboption stored procedure. Per Books Online, if this stored procedure does not work, we need to use the sp_removedbreplication stored procedure, which removes all the replication objects in the database.

Option 2 - ALTER DATABASE MODIFY FILE

In order to overcome shortcomings from option 1, and move the publisher's database files, we could use the alter database modify file command.

In this example, we will attempt to move the log file of a replication publication database r_pub. As a first step, we will get the information about the publisher database using the sp_helpdb system stored procedure. We need to collect the file name details so that we can use them in the alter database command. Below is an example of the data from sp_helpdb:

use the alter database modify file command in ssms

Based on the information above, we can run the following command in SQL Server Management Studio:

ALTER DATABASE r_pub
MODIFY FILE (NAME = r_pub_Log, 
             FILENAME = 'Enter valid path of drive location\r_pub_Log.ldf');

After this, open SQL Server Management Studio and navigate to Instance | SQL Server Agent | Job Activity Monitor and stop the replication log reader agent job and distribution agent job as shown below:

open ssma and navigate to instance sql server agent

We stop these jobs because we do not want the log reader agent to connect to the publisher database.

Our next step is to bring the publisher database offline. Use the command below to accomplish this task:

ALTER DATABASE r_pub SET OFFLINE

Once the publisher database is offline, move the log file to the new location in Windows Explorer as specified in the alter database command above.

After the log file is moved to the new location, bring the database online using below command:

ALTER DATABASE r_pub SET ONLINE

As a last step, go back to the SQL Server Agent Job Activity Monitor to start the replication jobs, log reader and distribution agent, as shown in the screen shot below:

go back to sql server agent job activity monitor

At this point you should see that the transactional replication is running without any issues.

Next Steps

Avoid using the sp_attach_db and sp_detach_db system stored procedures for attaching or detaching databases because the commands have been deprecated.
Get familiar with new options available in SQL Server like alter database with modify file, create database for attach, etc.
Check out all of the SQL Server Replication tips.

About the author

Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips

Comments For This Article

Wednesday, November 6, 2013 - 11:18:33 AM - Anwar

can you please explain how can we shrink and defrag publishing database data file without creating any issue with transactional replication. Huge transaction on the Publishing Database which is now 1 tb and removed 2 tables which gives 400+gb free space.

Thanks in advance

Helpneeded

Tuesday, April 23, 2013 - 5:46:26 PM - Syed	Back To Top (23531)
Very usefull and well thought. Thanks Mohammed for sharing it.

Thursday, January 31, 2013 - 12:14:37 AM - Mohammed Moinudheen

Hello John,

Not required really. However, please get this tested, just to make sure all is good.

Wednesday, January 30, 2013 - 1:37:56 PM - John	Back To Top (21802)
Is it necessary to turn off the distribution agent when doing the above steps? I would think just turning off the Log Reader agent is sufficient enough. I ask because my distribution agent is used for many other publications that are unrelated to the database I want to do a file move on.

Monday, July 30, 2012 - 7:02:17 AM - fotag	Back To Top (18845)
Very nice and to the point article!! Thanks a lot for your help !!!

Monday, June 20, 2011 - 6:20:14 PM - Papy Normand

Hello Mohammed,

Please, could you have a look at

http://msdn.microsoft.com/en-us/library/ms188031(v=SQL.110).aspx

Nowhere i found that sp_detach_db is not depreciated even for Denali

http://technet.microsoft.com/en-us/library/ms188031(SQL.110).aspx

I had had problems with the link you provide ( maybe it is coming from the documentation installed with a MSDN suscription on a computer )

Anyway, i am waiting for a next article which, i am sure , will be as interesting as this one

Have a nice day

Monday, June 20, 2011 - 4:28:14 AM - Mohammed Moinudheen

Papy,

Thank you for your comments. Regarding sp_detach_db, below is the excerpt from books online.

Important:

This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.

This is the link:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/abcb1407-ff78-4c76-b02e-509c86574462.htm

Thanks,

Friday, June 17, 2011 - 7:14:04 PM - Papy Normand

Maybe i am going wrong but according to http://msdn.microsoft.com/en-us/library/ms188031(v=SQL.105).aspx , it seems that only sp_attach_db is depreciated , sp_detach_db is not depreciated.

Anyway, the new way with alter database seems to be simpler as after the use of sp_detach procedure, we have to use a create database for attach ( 2 commands instead only one , and the create database for attach is not simple )

Thanks especially for the screen shots ( useful for people for whom english is not the "normal" language...

Friday, June 17, 2011 - 10:37:47 AM - Kotesh	Back To Top (14038)
Nice article on replication...crystal clear explaination with screen shots :-)