As per MSDN - "The Replication Log Reader Agent is an executable that monitors the transaction log of each database configured
for transactional replication and copies the transactions marked for replication from the transaction log into the distribution
database." Below is a collection of methods available using T-SQL, SQL Server Management Studio, SQL Profiler & Replication Monitor to view
the components, historical log records, status & configuration of the Log Reader Agent(s) you may have installed on your Microsoft
SQL Server systems.
T-SQL - Log Reader Agent - History Records:
USE Distribution
GO
SELECT
la.name as 'LogReaderAgent_Name',
la.publisher_db as 'Publisher_db',
CASE lh.updateable_row
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No' END AS 'History_Row_Updateable',
lh.start_time as 'Job_Start_Time',
lh.[time] as 'Message_Log_Time',
CAST(lh.comments AS XML) AS Comments,
CASE lh.runstatus
WHEN 1 THEN 'Start'
WHEN 2 THEN 'Succeed'
WHEN 3 THEN 'In-Progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retry'
WHEN 6 THEN 'Fail' END AS 'Run_Status',
lh.duration as Duration_Message_Session_sec,
lh.delivered_transactions as Delivered_Transactions_Per_Session,
lh.delivered_commands as Delivered_Cmds_Per_Session,
lh.average_commands as 'Avg_Cmds',
lh.delivery_rate as 'Cmd_Per_Sec',
lh.delivery_latency AS 'Delivery_Latency_ms',
lh.xact_seqno as 'Last_Processed_Transaction_Sequence#',
lh.delivery_time as 'Time_First_Transaction_Is_Delivered'
FROM MSlogreader_history lh WITH (nolock)
inner join MSlogreader_agents la on lh.agent_id = la.id
ORDER BY [time] DESC
--A Session is the activity/work/time since the LogReaderAgent SQL Job was started
--Select * from mslogreader_history
--Select * from MSlogreader_agents
Note:
History_Row_Updateable indicates those specific log records (comments column especially), will get updated as the Log Reader
Agent continues it's processing.
Last_Processed_Transaction_Sequence# (xact_seqno column) - if this value changes frequently it indicates the replicated
commands are being processed as you monitor it. You can find the actual commands of those transaction #'s using the T-SQL below.
Select ID, * from [dbo].[MSpublisher_databases]
Per TRX #:
EXEC distribution.dbo.sp_browsereplcmds @xact_seqno_start = '0x000177D500062F7E0001', @xact_seqno_end = '0x000177D500062F7E0001', @publisher_database_id = 3
All:
EXEC distribution.dbo.sp_browsereplcmds @publisher_database_id = 3
Log Reader Agent - Cmds Per Sec:
Select
t1.time,
t1.agent_id as 'AgentID',
t2.name as 'Agent Name',
comments
from Distribution.dbo.MSlogreader_history t1 with (nolock)
inner join Distribution.dbo.MSlogreader_agents t2 with (nolock)
on t1.agent_id = t2.id
where [time] > getdate() - 1
and comments like '%cmdspersec%'
order by time desc
declare @agent_id int = 8;
with cte_stats as(
select h.agent_id, ag.name, h.time, cast(h.comments as xml) comments
from Distribution.dbo.MSlogreader_history h with (nolock)
inner join Distribution.dbo.MSlogreader_agents ag with (nolock) on h.agent_id = ag.id
where
h.[comments] like '<stats state="1"%'
and h.agent_id = @agent_id
)
select
cte_stats.agent_id
,cte_stats.name
,cte_stats.time
,c.value('(/stats/@state)[1]', 'int') AS [state]
,c.value('(/stats/@work)[1]', 'int') AS [work]
,c.value('(/stats/@idle)[1]', 'int') AS [idle]
,c.value('(/stats/reader/@fetch)[1]', 'int') AS [reader_fetch]
,c.value('(/stats/reader/@wait)[1]', 'int') AS [reader_wait]
,c.value('(/stats/writer/@write)[1]', 'int') AS [writer_write]
,c.value('(/stats/writer/@wait)[1]', 'int') AS [writer_wait]
,c.value('(/stats/sincelaststats/@elapsedtime)[1]', 'int') AS [sincelaststats_elapsedtime]
,c.value('(/stats/sincelaststats/@work)[1]', 'int') AS [sincelaststats_work]
,c.value('(/stats/sincelaststats/@cmds)[1]', 'int') AS [sincelaststats_cmds]
,c.value('(/stats/sincelaststats/@cmdspersec)[1]', 'float') AS [sincelaststats_cmdspersec]
,c.value('(/stats/sincelaststats/reader/@fetch)[1]', 'int') AS [sincelaststats_reader_fetch]
,c.value('(/stats/sincelaststats/reader/@wait)[1]', 'int') AS [sincelaststats_reader_wait]
,c.value('(/stats/sincelaststats/writer/@write)[1]', 'int') AS [sincelaststats_writer_write]
,c.value('(/stats/sincelaststats/writer/@wait)[1]', 'int') AS [sincelaststats_writer_wait]
,cte_stats.[comments]
from cte_stats
cross apply cte_stats.comments.nodes('/stats[@state="1"]') b(c)
order by cte_stats.agent_id, cte_stats.name, cte_stats.time desc;
Replication Monitor - Log Reader Agent - Current Status:
Replication Monitor - Log Reader Agent - Profile Configuration:
T-SQL:
EXEC Distribution.dbo.sp_help_agent_profile @agent_type = 2
/*
1 - Replication Snapshot Agent
2 - Replication Log Reader Agent
3 - Replication Distribution Agent
4 - Replication Merge Agent
9 - Replication Queue Reader Agent
*/
EXEC Distribution.dbo.sp_help_agent_parameter @profile_id = 2
SQL Server Profiler - sp_replcmds & sp_MSadd_replcmds calls
LogReader Reader Thread is reading the Transaction Log via stored procedure sp_replcmds, a wrapper for xp_replcmds. It scans the
transaction log for transactions marked for replication, skipping not replicated transactions.
LogReader Writer Thread writes queued transaction from the Reader thread into the Distribution database using sp_MSadd_replcmds.
Log Reader also executes a “History Thread" writing summary data to MSlogreader_history system table of the distribution database.
SSMS - Log Reader Agent - Status:
Log Reader Agent - Executable (logread.exe)
Resources:
Replication Log Reader Agent
MSlogreader_history (Transact-SQL)
sp_replcmds (Transact-SQL)
Replication Agent Profiles
How to: Work with Replication Agent Profiles (Replication Transact-SQL Programming)
How Transactional Replication Works
|
|
|
|
|
|