SQL server replication – how long??

so, you have replication running on MS SQL Server; you just made a whole load of updates which were wrapped up in a transaction; you’ve applied the transaction and now replication is going about it’s way… but how long will it take??

well, there are a few issues here – firstly, the log reader agent needs to scan the SQL log to see what changes need to be replicated, and that will take time. once it has decided what needs to be replicated, it needs to add the commands to the distribution database, and that will take time too…. once all *that* is done, it can start to deliver the replicated commands, and we can certainly see how fast that is going 🙂

we can query the distribution database like so:

SELECT *
FROM distribution.dbo.MSDistribution_history
ORDER BY time DESC

and the crucial column are looking for would be the comments column as detailed below:

<stats state="2" fetch="7" wait="44628" cmds="140239" callstogetreplcmds="1">
 <sincelaststats elapsedtime="307" fetch="7" wait="307" cmds="972" cmdspersec="3.000000"/>
</stats>

by default this table is updated every 5 minutes – elapsedtime=”307″ (shown in seconds) – which means we can see at 5 minute intervals how the replication susbsystem is performing. you may notice the comment return here is XML formatted, so we may as well capture the most recent comments as an XML variable for shredding later, as shown below:

DECLARE @XML XML = (
 SELECT TOP 1 comments
 FROM distribution.dbo.MSdistribution_history H 
 WHERE comments LIKE '<stats%'
 ORDER BY H.time DESC
 )

if processing is complete when the MSdistribution_history table is updated, we will see state=”1″, otherwise if it is mid-process it will be 2; this provides different information structures, with the mid-process also reporting how many commands have so far been applied, as shown below:

<stats state="1" work="50664" idle="2515">
 <reader fetch="745" wait="50566"/>
 <writer write="50664" wait="739"/>
 <sincelaststats elapsedtime="304" work="0" cmds="0" cmdspersec="0.000000">
  <reader fetch="74" wait="0"/>
  <writer write="0" wait="74"/>
 </sincelaststats>
</stats>

<stats state="2" fetch="15" wait="4802" cmds="667171" callstogetreplcmds="1">
 <sincelaststats elapsedtime="300" fetch="14" wait="300" cmds="44928" cmdspersec="149.000000"/>
</stats>

now, you may notice the second output – where state=”2″ – has 2 attributes labelled cmds – the first (inside the stats element) shows a running total of applied commands (cmds=”667171″) whereas the second (inside the sincelaststats element) shows how many were applied since the previous table entry. so we query the state and set a variable accordingly:

DECLARE @state INT = (SELECT tab.col.value('@state', 'INT ') AS [State]
FROM @XML.nodes('/stats') AS tab(col))

both outputs show us the rate commands have been applied for the last 5 minutes – cmdspersec -as shown below:

DECLARE @cmdspersec INT = (SELECT tab.col.value('@cmdspersec', 'FLOAT') AS [CmdsPerSec]
 FROM @XML.nodes('/stats/sincelaststats') AS tab(col))