We have a DB in stress environment: SQL Server 2014 (SP1-CU3) + Always ON

The whole db is around 850GB, but in some point something happens and in memory FG is growing to 250GB and a log to 450GB (original sizes around 1 and 5GB).
It happens fast - I can see it by checking log backups in 5 minutes backup files are growing from 20kb to 200mb and in another 5 to 500MB and "stays" in this area. Log stays full and cannot be shrunk.
Log reuse wait shows AVAILABILITY_REPLICA.
After removing this DB from AON log reuse wait changes to XTP_CHECKPOINT, but still nothing happens.
Even changing recovery model to simple doesn't help.
Only after executing checkpoint, in memory FG size reduces slowly till around 1GB size.
Afterwards the log can be shrunk also.
Does anyone familiar with this behavior? What would you suggest to monitor to find a root of this problem?

  • The log_reuse_wait of availability replica means we have un-sent log blocks that other replicas need. This is a symptom, not a cause. The root cause is the IMOLTP growth issue. Since it's in a stress environment, what is being run? I find it hard to believe on an idle instance the table just grows. – Sean Gallardy - Retired User Jul 28 '16 at 2:23
  • @SeanGallardy thanks for the response, as far as I know nothing special, that don't run on others envs, I'm monitoring queries on this DB now to find out – Michael Cherevko Jul 28 '16 at 6:04
  • Additionally, please make sure you're on at least 2014 SP1 CU4 - connect.microsoft.com/SQLServer/feedback/details/1021865/… – Sean Gallardy - Retired User Jul 28 '16 at 15:37
  • I've upgraded sql server to sp2 log reuse wait now is LOG_BACKUP, it runs every 5 minutes and generates(compressed) log backup of 600-700MB with no activity to justify it. in memory FG is 25GB now. – Michael Cherevko Aug 21 '16 at 12:53

Your Answer

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

Browse other questions tagged or ask your own question.