MS-SQL transactional replication - How to figure out how much data is being pushed per day in replicated databases

Question

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?

Answer 1

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.