0

I would like to ask what is the meaning of pass #1, pass #3 in SQL Server replication monitoring, when the log reader agent scan the transaction log with huge log records (as showed in the attached screenshot). Does SQL Server also have pass #2, pass #4 ???, I've never seen it before.

replication monitoring screenshot

Lukas85
  • 1
  • 2

1 Answers1

0

Don't worry about the numbers. This is an indication that you have a lot of non-replicated transaction in your publisher database transaction log. Check if you are doing any index maintenance on your publisher database or bulk processing of data with tables that are not part of your transactional replication.

Number of things you can check:

  1. Check how big is your t-log on publisher: DBCC SQLPERF(logspace) pre-size your t-log accordingly. Tip: I would check my biggest index size or start with 8GB and autogrowth to 8GB.
  2. Check the publisher Virtual Log File (VLF): DBCC LOGINFO make sure you run it on the correct database.
  3. Check your backup job is properly running
  4. Make sure your t-log is is health.
  • Thanks dco for your answer. and yes this is the result of index re-organize. Sure, we follow all best practices to get high transaction log throughput. But I still want to know what does that messages mean, what is the pass #1,3 indicate for. Any link to any articles/doc would be high appreciated, thanks – Lukas85 Jun 27 '18 at 09:03
  • @Lukas85 number of scan on the log record if I’m not mistaken. when you see that message it means the log reader is scanning the t-log file multiple times sequentially(find the object for replication). I dont think this is documented though. –  Jun 27 '18 at 09:34