0

Environment details: SQL Server 2019 (CU 19) - 96 logical processors, 1.5 TB RAM Hosting 1 database of 3 TB

I have distributed availability group (async) setup which spans across two datacenters. Both sides (Primary and Forwarder AGs) are 2 node cluster with sync replica.

Everything is running smooth all day but sometimes I see huge log_send_queue_size (upto 7 GB). It clears up in couple of minutes at good rate, so I don’t suspect bandwidth to be bottleneck here.

What I am trying to figure out is what is generating so much log. And what should I use to track it (Extended events may be?). Any help would be appreciated.

Improve this question
1
  • 2
    An extremely simple method is to look at the transaction sizes in the previous log backups for the time range and then see what the largest were doing. If you aren't already using XE or some other logging to grab replica/database level flow control, that would need to be added as well.
    – Sean Gallardy
    Commented Nov 11, 2023 at 15:37

1 Answer 1

Reset to default
1

Followed Sean's advice and noticed pattern of big log backups compared to other times.

So, I dug couple of log backups using fn_dump_dblog (WARNING: undocumented) where I saw the spikes and it seems query store was doing a lot of LOP_INSERT_ROWS into plan_persist_runtime_stats in all the cases. I will try to adjust capture policy.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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