Moving database files for a replicated SQL Server database

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


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.


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.


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:

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:


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:


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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, November 6, 2013 - 11:18:33 AM - Anwar Back To Top (27426)

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



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 Back To Top (21816)

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 Back To Top (14059)

Hello Mohammed,

Please, could you have a look at

Nowhere i found that sp_detach_db is not depreciated even for Denali

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 Back To Top (14044)


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


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:



Friday, June 17, 2011 - 7:14:04 PM - Papy Normand Back To Top (14041)

Maybe i am going wrong but according to , 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 :-)

get free sql tips
agree to terms