We use MS SQL Server transactional replication in our environment. I would like to know if there is a way or script to figure out how much data is being pushed per day to replicated databases?

How to determine how much data is being replicated on a daily basis based on the number of commands?


In the distribution database, there is 2 tables that could help you:

  • MSrepl_transactions
  • MSrepl_commands

For example, you could get the number of commands replicated per day per publisher database with this query:

select t.publisher_database_id,convert(date,entry_time) as entry_date, count(*) as nb_commands 
from MSrepl_transactions t
    INNER JOIN MSrepl_commands c ON t.publisher_database_id=c.publisher_database_id and t.xact_seqno=c.xact_seqno
GROUP BY t.publisher_database_id,convert(date,entry_time) 

2 details to be aware of:

  • By default, transaction retention period is 72 hours, so you should be able to get statistics for that period, unless your distributor has a different configuration.
  • MSrepl_transactions' entry_time is the time when the transaction was inserted into distribution database, not the execution time on publisher database.
  • 1
    Additionally useful would be analyzing SUM(DATALENGTH(c.command)) (or SUM(DATALENGTH(c.command/1024.0/1024.0))) to get the total size of the commands being pushed across replication to estimate the "how much data" in bytes (or megabytes).
    – AMtwo
    Sep 13 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.