info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Replication, Cloud & Migrations



Move Distribution Database Files to other drives
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2015-11-01







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)