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.
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:
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:
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:
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.
Mohammed has been a SQL Server DBA since 2005 and has had the opportunity to work on different versions of SQL Server starting from SQL Server 2000 with exposure to Sybase as well. He is based in Wellington, New Zealand and works at Datacom Systems Ltd, as a SQL Database Administrator. He can be reached at: moinu@live.com.
- MSSQLTips Awards: Author of the Year Contender – 2016, 2018-2021 | Achiever (75+ tips) – 2021