How to resolve when Distribution Database is growing huge

huge database is kind of relative, but generally if you see Distribution database growing more the 25gig it means the Cleanup processes is having a hard time deleting replicated transactions.  I’ll cover the how and why on Cleanup processes later, but for now I wanted to post a technique we’ve used to purge rows from the Distribution database.  This solution involves modifying the SQL Replication stored procedures to increase the number or rows being deleted per transaction.  If you’re uncomfortable making the code change, skip down to STEP 7).

This first posting coverage a “conservative�� approach.  Later I’m post steps for a more “aggressive” solution.

1) script msrepl_commands cleanup proc and save original sp code

sp_helptext  sp_MSdelete_publisherdb_trans

2) change from CREATE to ALTER

ALTER PROCEDURE sp_MSdelete_publisherdb_trans

3) change all 3 locations from 2000 to 100000 rows

DELETE TOP(2000) MSrepl_commands . . .

4) script msrepl_transaction cleanup proc and save original sp code

sp_helptext sp_MSdelete_dodelete

5) change from CREATE to ALTER

ALTER PROCEDURE sp_MSdelete_dodelete

6) change both locations from 5000 to 100000 rows

delete TOP(5000) MSrepl_transactions . . .

7) Determine oldest day containing transactions

--(shows breakout by day, by hour.  Took 2 hours on 350million rows, 100gb distribtuion db)
SELECT T.[publisher_database_id]
,datepart(mm,[entry_time]) 'month'
, datepart(dd,[entry_time]) 'day'
, datepart(hh,[entry_time]) 'hour'
    ,count(C.[xact_seqno]) 'count of commands'
FROM [distribution].[dbo].[MSrepl_transactions](nolock) T
JOIN [MSrepl_commands](nolock) C
ON T.[xact_seqno] = C.[xact_seqno]
GROUP BY T.[publisher_database_id]
  ,datepart(mm,[entry_time])
  , datepart(dd,[entry_time])
  , datepart(hh,[entry_time])
order by 1,2,3,4
--Or, just select oldest 10 rows and note the entry_time stamp.
--(select took 5 minutes on 350million rows, 100gb distribtuion db)
SELECT TOP 10 * FROM [distribution].[dbo].[MSrepl_transactions](nolock)

8) Execute cleanup via SSMS or a TSQL job to delete JUST oldest day.  (24 hours @ 5 days = 120), then continue to reduce the @max_distretention valued by a few hours for each run.

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

Example output: (4 hours to removed 340million rows)

Removed 3493 replicated transactions consisting of 343877158 statements in 15043 seconds (22859 rows/sec).

Hope you found this helpful,