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.

Cause

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.

Updated May 2017 :

Someone asked me, how do we speed up the LogReader reading the transaction log, correct question should be “How can we speed up SQL Server, the process which actual does the read, then passes the results to the LogRead.exe application?  The answer to that question is place the database transaction log on fastest storage you can afford.  Next is control the amount of activity you write into the transaction log.  For example,  is your web application over chatty, is it saving data too frequently causing high volume of WRITEs to the transaction log?  Fewer writes, less stress, better performance for other activities like reading the data on behalf of the LogReader.