Moving log files in publishing and replication mode in SQL Server Series

I. Cause

With the increasing number of database files, the storage space on the disk becomes increasingly insufficient. To improve the performance of the database, migration of database log files is particularly important, that is, the primary and log files of the database are stored on different disks.

 

Ii. Try migration

The first method to migrate log files is the Detach and Attach methods. You can use either of the following methods:

1. Script

SP_DETACH_DB 'DB NAME', 'TRUE'

You will find the following errors

2. Use the SSMS Manager

Note the Status and Message columns.

I checked MSDN and found that this is the case. If the database has publishing replication, it cannot be completed through Detach and Attach (unless you remove publishing replication first ).

Iii. Problem Solving

1. Run the following command to obtain basic database information:

SP_HELPDB DB_NAME

2. Execute the following script

ALTER DATABASE DB_NAMEMODIFY FILE (NAME =DB_NAME_log,              FILENAME = 'D:\DB_Log\DB_NAME_log.ldf');

3. Stop the replication log reader agent job and distribution agent job

4. Offline database publishing (if the time is too long, you can stop the SQL Server service and then restart the service so that all database connections and operations can be quickly stopped)

ALTER DATABASE DB_NAME SET OFFLINE

5. Move the log file to Step 1 (Directory D: \ LMS_DB \)

6. Publish database Online

ALTER DATABASE DB_NAME SET ONLINE;

7. Start two jobs stopped in step 1