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



Distribution Database in Recovery Pending State
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2018-12-03








Issue:

Cluster Storage was changed for Storage Spaces direct and caused an outage to Distribution Database for SQL Server Transactional Replication.



Fix:


Step 1: Check existing database files location:

USE Master;
go

Select * from sys.master_files


Step 2: Modify the Distribution database mdf and ldf file paths


USE Master;

ALTER DATABASE [Distribution]
MODIFY FILE (name='distribution',filename='C:\ClusterStorage\Volume1\MSSQL13.MSSQLSERVER\MSSQL\DATA\distribution.MDF'); --Filename is new location

ALTER DATABASE [Distribution]
MODIFY FILE (name='distribution_log',filename='C:\ClusterStorage\Volume1\MSSQL13.MSSQLSERVER\MSSQL\DATA\distribution.LDF'); --Filename is new location
go

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 3: Re-Start SQL Service