Why do consecutive .bak full backup files increase in size?

Question

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.

Answer 1

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).