Share via

maintenance of distribution database in transactional replication

Heisenberg 261 Reputation points
2022-06-24T22:20:46.597+00:00

hi Folks,
What kind of maintenance job do you run on Distribution database in transactional replication? Our distribution db cleanup job that cleans up msrpl_commands and msrpl_transactions commands runs very slow. Particularly it takes long time to run "delete top X " command from msrepl_commands table. Do i need to update statistics on this table periodically or run defrag on it?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 21,131 Reputation points Microsoft Vendor
    2022-06-27T07:03:13.523+00:00

    Hi @Heisenberg ,

    Configure the Daily Index Reorganization or Maintenance jobs to include the distribution database. The database involves huge data insertions into the MSrepl_transactions and MSrepl_commands tables.

    Note: Continuous polling on these 2 tables and DELETE from them after sending data successfully to the Subscriber database increases the risk of fragmentation. Rebuilding these tables on a scheduled basis can improve the distribution database performance.

    Configure the Daily or Routine Backup jobs to include the distribution database for reference purposes or troubleshooting in case of any data corruption or loss.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".