Question :
Index maintenance usually starts 3 hours before the database full backup and ends before the full backup start.
The database is using the Simple Recovery model.
Last weekend the index maintenance took more than 5 hours and the full backup was running at the same time.
We have noticed that since the full backup started the transaction log File started to grow every 30 Seconds until it filled the disk and both full backup and maintenance failed due to insufficient disk space for log file.
Why, when the backup has started while index reorganize is running, does this happen to the transaction log file?
Answer :
When a database backup occurs, it also includes portions of the transaction log.
I’m quoting from Paul Randal’s post More on how much transaction log a full backup includes. (Highlighting mine)
Now, the reason that the transaction log is read is so that the
restore operation can recover the database so it is transactionally
consistent as of the point in time when the read data operation
completed.So, (ignoring replication) the minimum LSN of the transaction log
that’s included in the full backup is MIN (LSN of last checkpoint, LSN
of oldest active transaction). This ensures that recovery can REDO log
records to bring pages up-to-date and UNDO log records for
transactions that had not committed.
Your index maintenance is changing data pages that your backup may have already read and included in your backup. Once a data page is read by the backup, it is not read again.
In order for SQL Server to recover the database so it is transactionally consistent as of the point in time when the read data operation completed, the log cannot be truncated until those transaction log records have been included in the backup and the log would continue to grow and possibly run out of space.