Customize Distribution Cleanup Batch Size

If Distribution database is growing more the 100gig it might indicate Distribution Cleanup processes is either blocked or deleting slower then rows are being added. Latest Cumulate Updates of Server 2014, 2016, and 2017 allow you to customize the cleanup batch size via updated 17.x SQL Server Management Studio under the Distributor properties dialog.

Using SSMS, Replication, Distributor Properties, clicking “…” expand, you can customize the Transaction and Commands delete batch size to delete in larger increments during distribution database cleanup. Try increasing by 10x or 100x than monitor row counts and blocking.

On my test changing to 100000 rows clean up completed in about 4 hours and removed 340million rows.

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