7

Do any of the DMVs or is there another way to expose what kind of data is in the Redo Queue that's in process of being synced up to a secondary replica? (E.g. is it table data and which table, or is it index changes and which indexes, etc?)

9

The short answer is no, there is not a DMV that contains this information. It would be a significant amount of overhead for SQL Server to track what table was associated with each log record that had been received but not committed.

As you can read in Reading the transaction log in SQL Server – from hacks to solutions, you can read the log records with a function, but it's certainly not something that you would want to do in production just to have some interesting information. Doing something like this on every log record in real time would add a tremendous amount of I/O load on a busy server, and you wouldn't want it to be done unless it solved a significant problem.

Although the type of information you're looking for would be interesting, you might get a better answer if you can state why you want to see this information--there may be a better solution.

  • My particular case was my Redo Queue was abnormally large one morning, so my secondary replica was out of sync with my primary and I wasn't sure of the best way to debug what happened. I eventually realized the night before into the early morning my index maintenance job (on the primary) ran for 12 hours straight before completing and therefore must've generated a large amount of transactions that the secondary had to catch up to. Luckily the secondary did catch up a few hours later, but it would've been nice to have some explicit way to determine the cause of the transactions at the time. – J.D. Nov 23 '19 at 13:39
  • 1
    I see that symptom sometimes on replicas that are across the WAN link, particularly after there's been a WAN outage. We just restart the SQL Server service. I think to diagnose it you'll have to open an incident with Microsoft and they will have to find out what thread is stuck or died or whatever. – Tony Hinkle Nov 23 '19 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.