Recently I was tasked with relocating Microsoft SQL Server 2008 Distribution Database files located on C:\
to separate drives. The Dist DB was over 30GB in size with the MDF and LDF. The replication setup
was quite busy in terms of the volume of transactions sent over the wire. Below are the steps I used and
it was a successful change.
Solution:
Step 1:
Stop Log Reader Agent Job & Distribution Agent(s) Job(s)
- Can do this from Job Activity Monitor
Step 2:
Make note of the current Distribution MDF and LDF file locations
Master DB:
ALTER DATABASE distribution SET OFFLINE
Step 3:
Physically move the Distribution MDF(data) and LDF(log) files to the new location(s)
Step 4:
Master DB:
ALTER DATABASE distribution MODIFY FILE ( NAME = distribution, FILENAME = 'L:\distribution.mdf')
go
ALTER DATABASE distribution MODIFY FILE ( NAME = distribution_log, FILENAME = 'M:\distribution.ldf')
go
Output will be:
The file "distribution" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "distribution_log" has been modified in the system catalog. The new path will be used the next time the database is started.
Step 5:
ALTER DATABASE distribution SET ONLINE
Step 6:
Start Log Reader Agent Job & Distribution Agent(s) Job(s)
|
|
|
|
|
|