0

First, this is NOT a duplicate of this question. In that question the person was over-writing the file each time and the solution is to use INIT or NOINIT.

In my case we are generating a unique filename with each backup - a timestamp is appended to the filename.

We have an agent job that runs full backups nightly for about 20 databases. For about a week, 1 database seems to be increasing in size significantly each night.

We run a second agent job that runs log backups every 15 minutes but stops running between about 11pm and 5am. Usually first thing in the morning the log backups are large and then their size is small until people start working and then they're a variety of sizes.

All of this has been working without issue for years.

Only one database out of the 20 is showing this issue - each night's full backup is significantly larger than the prior night.

The log file backups are working for this database and all the others.

We have an availability group with three replicas. Two are set up to fail over to each other and the third replica is read-only for heavy queries, data extracts and reporting. The AG console shows green checkmarks all the way down the page for all servers.

The database in question is the largest out of the 20. Normal full backup is about 25GB. It slowly increased as follows: 33, 35, 36, 41, 47, 54, 64, 73 GB.

In contrast, for all 14 backups prior to this issue, file size was fairly steady around 25GB.

I have just paused all log backups for all database and issued a full backup to the affected database and that last full backup is 76GB.

15
  • What is the size of the data file and the log file of the database? What were they when the backup was only 25 GB?
    – J.D.
    Commented May 7, 2023 at 23:51
  • @J.D. - currently data file is 207GB and log is 220GB. Commented May 8, 2023 at 0:23
  • You should add updates like that to your original Post so more people see it. The next couple of times your backup file grows significantly, keep an eye on if your data and log files are growing as well, and please let us know.
    – J.D.
    Commented May 8, 2023 at 1:36
  • 3 things to check since you ruled out that you are not writing to the same file - 1. data growth (if data is being written excessively due to some app bug), 2. Index fragmentation is very high (a rebuild or reorg can help) and 3. if LOB columns are added recently (schema changed occurred recently)
    – Kin Shah
    Commented May 8, 2023 at 1:37
  • 1
    @J.D. - I've managed to resolve the issue. Fundamentally your suggestion to check log_reuse_wait_desc put me on the right path. Even though it was the backup file of the data file that was growing, somehow the log files were linked. The two primary AG replicas both showed they were waiting on AVAILABILITY_REPLICA but a third replica - read only - showed it was waiting on ACTIVE_TRANSACTION. That said, when I queried - no active transactions. Finally, rebooted that replica and all resolved although log file remained 99% in use and most VLFs status 2 despite backups. Shrink file still worked Commented May 10, 2023 at 3:57

1 Answer 1

0

This seems to have been a particular issue where sys.databases.log_reuse_wait was reporting AVAILABILITY_REPLICA on two replicas in the Availability Group, and ACTIVE_TRANSACTION on the third replica.

In my case, trying to determine the active transaction yielded no results - read the whole comment thread against the question for details.

In the end I rebooted that replica and all resolved.

In summary

The log file was being held open by an active transaction (or at least the database thought so) and this somehow was flowing on to the data file full backup filesize growing nightly.

Identifying the root cause came through checking sys.databases.log_reuse_wait - with thanks to @J.D. in the comments for the suggestion -

And the resolution came through rebooting the affected replica (because I could not actually find any active transaction to kill, despite the log_reuse_wait message saying ACTIVE_TRANSACTION).

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.