0

The Transaction Log Usage fluctuating quite frequently.

When I run DBCC SQLPERF('LOGSPACE') it shows Log Space Used (%) as 3% which is like 650MB while at very next minute or two when I run it again it might show as 0.0123% without doing any kind of Log backup or anything and it happened few times.

Just wondering what it could be? I was trying to analyse Log usage pattern over the period of time.

Thanks in advance

Improve this question
7
  • 1
    What is the recovery model on your database? – Tom V Apr 28 '17 at 9:21
  • its Full recovery model – Sajid Apr 28 '17 at 9:25
  • Are there any noteworthy messages in the sql error log around that time? – Scott Hodgin Apr 28 '17 at 10:01
  • Do you happen to have any kind of mirroring or replication involved in this scenario? – Scott Hodgin Apr 28 '17 at 10:06
  • no, I could not see any noteworthy message apart from quite few 'Login failed for user' messages. This database is part of High availability group though. – Sajid Apr 28 '17 at 10:07
1

The information in your question leads me to believe that some kind of log truncation is occurring between your first invocation of DBCC SQLPERF('LOGSPACE') and your second invocation. According to The Transaction Log, (specifically the section Factors that can delay log truncation), there are numerous things that can prevent log truncation. You mentioned the fact that the database is in a High Availability Group. I'd suggest periodically querying the sys.databases catalog view (log_reuse_wait and log_reuse_wait_desc columns). Perhaps "a secondary replica of an availability group is applying transaction log records of this database to a corresponding secondary database. and when that completes, the VLF's are marked for reuse."

Improve this answer

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.