Distribution Database Growth is huge in Merge Replication

Distribution Database Growth is huge in Merge Replication



looked into the distribution database tables and found dbo.MSmerge_history table has 25550863 Number of records.

output of dbcc SQLperf(logspace) was:-

Database Name !Log Size(MB) !Log Space used(%)  ! Status
distribution           !     43686.55     ! 101.1327                !   0



when executed this command manually

use distribution
go

EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72

got message like this :- 

Removed 0 replicated transactions consisting of 0 statements in 0 seconds (0 rows/sec).
                            or

Executed as user: NT AUTHORITY\SYSTEM. The transaction log for database 'distribution' is full due to 'ACTIVE_TRANSACTION'. [SQLSTATE 42000] (Error 9002).  The step failed.

-- The reason is Distribution database log file are placed in a  smaller disk, so the job never complete and you stuck in a situation what to do? 

one side high number of dbo.MSmerge_history table records and other side when you execute the job, the job gets fail.

hence resolution is to stop the job and execute below code manually..


hence I deleted the records manually 


WHILE 1=1
BEGIN
   SET ROWCOUNT 10

   DELETE dbo.MSmerge_history
   FROM dbo.MSmerge_history msmh
   JOIN dbo.MSmerge_sessions msms
   ON msmh.session_id = msms.session_id
   WHERE msms.end_time <= getdate()
   OPTION(MAXDOP 1)
   
   IF @@ROWCOUNT = 0
       BREAK
END   


This script will take time, but it will clean this table. 

Then we can shrink the log files. 

USE [distribution]
GO
DBCC SHRINKFILE (N'distribution_log' , 1024)

GO