Moving database files for a replicated SQL Server database

By: Mohammed Moinudheen | Read Comments (4) | Print Mohammed is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies. Related Tips: 1 | 2 | 3 | 4 | 5 | More

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.

Share:

Share

Tweet

Free SQL Server Learning The SQL Server Security THREAT - It’s Closer Than You Think Tutorial - SQL Server Performance Tuning and Monitoring

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code