Impact on Log Reader Agent after reindex operations
José Moreira Neto
Microsoft SQL Server Escalation Engineer
Certain table reindex operations, like online reindex or reindex operation while database is running in full recovery model, will create large amounts of transaction log records in the database transaction logs. This may or may not generate an actual increase in the physical size or the transaction log file. If the log file was large, but empty, the reindex transactions will fill available space log space without appearing to grow the physical size of the log.
The Log Reader Agent will scan all transaction log records even those that are created by the a reindex statement. It will appear like the Log Reader Agent is “hung” while it is scanning the reindex log records. You may see message like “no response in last 10 minutes” recorded in the LogReader Agent history viewable in Replication Monitor.
You will also notice Tracer Tokens are not being replicated. This gives the impression that the SQL Transactional Replication LogReader is hung. However, you can confirm in Windows Task Manager that LogRead.exe is still showing CPU activity.
The LogReader can’t skip-ahead, but must read through reindex records recorded in the database’s Transaction Log. Once LogReader has scanned over the reindex transaction log records it will continue to processes table INS/UPD/DEL SQL commands. Depending on the number of non-replicated transactions it may take 10s minutes or longer until next data update is replicated.
You should plan your reindex maintenance in a way that does not impact Transactional Replication latency, or use reindex options that generate less records in the transactions logs.