0

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?

  • 1
    Instead of just blindly trusting that index maintenance will always finish in some specific amount of time, why not just chain the two events together? Eg the last thing the maintenance job does is kick off the backup job. Point in time recovery can’t be a high priority, or you wouldn’t be using simple recovery. – Aaron Bertrand May 20 '18 at 14:06
3

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.

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.