Sql-server – Unexpected Transaction Log Flush events and LOG_BACKUP errors

availability-groupsbackupsql serversql server 2014transaction-log

I recently had an unexpected Transaction Log event which I need assistance unpacking some of the event details. I have the what, but not the why…

The scenario is: Unattended application writing to Always On AG backed database, flushing about 55MB of Transaction logs every few minutes.

The database Transaction log size is 8GB, but <100MB is used. Log backups are every hour (as this is DEV) and this has been sufficient (up to now).

Within a 15 minute window, almost 20GB (unexpectedly) is flushed to the log. The drive the log is on fills up and "Full due to LOG_BACKUP errors" start spewing into the log.

The next scheduled log backup occurs at top of the hour, creates 3 GB compressed backup. The LOG_BACKUP errors stop, but transaction log used space remains at 20GB.

30 minutes later, Transaction log in use drops suddenly from 20GB to 1.5GB. No backup occurring during that time.

The application logs so far haven't been a huge amount of help in determining where those transactions have come from and why they flushed all at once to the log.

AlwaysOn Log and Redo queues also stayed at 0 during this event.

If the backup wasn't enough to free any space and all those transactions were still open and only rolled back 30 minutes (assuming they were as there was no log backup), why didn't the LOG_BACKUP errors continue after the backup?

Best Answer

Did you have a look at log_reuse_wait_desc in sys.databases?

We had a similar issue and I have asked a similar question.

Unable to truncate transaction log, log_reuse_wait_desc - AVAILABILITY_REPLICA