Microsoft SQL Server Escalation Services
SQL Server Transaction LogReader Agent timeout executing sp_replcmds occurs most often when a large transactions has been written to the Published database transaction log, or there are a large number of un-replicated commands. Either problem requires the LogReader to read more log entries than the timeout parameter allows causing the “query timeout expired” while executing sp_replcmds.
Use these commands below to explore the transaction log to see if your log file matches either of these patterns.
--Troubleshooting LogReader Timeout executing sp_replcmds
--Find last distributed and "next to" distribute transactions.
USE <published database>
Replicated Transaction Information:
Oldest distributed LSN : (54:4986:1)
Oldest non-distributed LSN : (55:4803:2)
--Count number of pending to replicated log entries per transaction
select [Transaction ID], count([Transaction ID]) as 'Count log entries'
from::fn_dblog('55:4803:2',null) -- Oldest non-distributed LSN : (55:4803:2)
where [Description] = 'REPLICATE'
group by [Transaction ID]
Transaction ID Count log entries
Is the pattern of cmds/transaction what you expect for your application?
Are there a large number of logs records per given transaction which can account for the LogReader Timeout?
If a large number of cmds/transaction occur, increasing the query timeout for the LogReader Agent Profile is recommended solution. You can also set the Agent ReadBatchSize = 1 and VerboseHistory = 2 to get more feedback from the Agent.
For a single transaction with 100K commnds, the LogReader history may show “waiting for response”. You can execute sp_who2 and look for ProgramName = <log reader job> and see if the CPU, and IO counters continue to climb. If these counters are increasing, the LogReader is still processing the transaction log.
Remember to set Agent back to default setting once it has processed the large batch.
Replication Log Reader Agent
Using “Verbose history agent profile.” while troubleshooting Replication