Extracting metrics from Distribution.dbo.MSlogreader_history messages "Approximately 45000000 log records have been scanned in pass # 4, 0 of which were marked for replication."
What % of total log records scanned are marked for replication?
What are the total commands delivered over each delivery interval?
The below script provides insights into a system under heavy insert/update/delete activity, but very little of the Publisher Database Transaction Log Records are marked for replication. If high # records being scanned and a low % are marked for replication, common options to improve Log Reader throughput are increase the ReadBatchSize of the agent, perform re-indexing during maintenance window or re-architect replication and place the articles into their own database if your setup has few tables out of many configured for replication.
Select
t1.Time,
t1.agent_id as 'AgentID',
t2.name as 'Agent Name',
Comments,
Delivered_Commands,
Convert(int,delivered_commands - LAG(delivered_commands, 1,0) OVER (ORDER BY YEAR([time]))) AS 'Commands Added',
Substring(comments,Charindex('y',comments)+1, Charindex('log ',comments)-14) as 'Log Records Scanned',
LAG(Substring(comments,Charindex('y',comments)+1, Charindex('log ',comments)-14),1,0) OVER (ORDER BY YEAR([time])) as 'Previous Log Records Scanned',
Convert(Decimal(18,3),Convert(Decimal(18,3),(delivered_commands - LAG(delivered_commands, 1,0) OVER (ORDER BY YEAR([time]))+1)) / Convert(Decimal(18,3),(LAG(Substring(comments,Charindex('y',comments)+1, Charindex('log ',comments)-14),1,0) OVER (ORDER BY YEAR([time]))+1)) * 100)
as '% All T-Log Records marked for REPL'
Into #TMP
from Distribution.dbo.MSlogreader_history t1 with (nolock)
inner join Distribution.dbo.MSlogreader_agents t2 with (nolock)
on t1.agent_id = t2.id
where t1.agent_id = 8
and comments like '%marked for replication%'
order by time asc
Select * from #TMP where [% All T-Log Records marked for REPL] < 100.00
Drop Table #TMP
|
|
|
|
|
|