SQL Server Index Reorganize During Full Backup – Best Practices

backupindexsql serversql-server-2008

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?

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

Related Question