1

Context

Buried deep in this documentation, we find

a log file specified with unlimited growth has a maximum size of 2 TB

Suppose that I want to rebuild a single non-partitioned 5 TB index ONLINE that belongs to a table in a database with the FULL recovery model on my Enterprise Edition box. The recovery model cannot change because it is in an AAG and is also not my decision to make.

The Problem

Does the limit on the size of the transaction log render it impossible to rebuild such an index? If it is not impossible, then should any steps at all be taken to mitigate dramatically growing the transaction log?

What I've Tried

I've tried to test this myself, but the non-production clones of this server are shared with hundreds of other people and don't have transaction log backups. This means I'm just as scared of this rebuild breaking them as I am of breaking the actual production box. Very early in my career, I saw production boxes broken by lack of care with the transaction log. What tests I've done certainly indicate that running such a rebuild causes the transaction log to grow rapidly. I'm scared.

I also lack the hardware to test this on my personal machines. I don't have 10 TB of storage sitting on my desk!

I've tried to Google around the problem and found nothing to help. It is almost as if the problem that I'm worried about doesn't even exist.

J. Mini
  • 1,161
  • 8
  • 32

2 Answers2

1

As of SQL Server 2017, you don't need to worry, but only on Enterprise Edition.

Resumable index rebuilds are specifically designed to solve this problem.

From the above link

Resumable index create or rebuild doesn't require you to keep open a long running transaction, allowing log truncation during this operation and a better log space management. With the new design, we managed to keep necessary data in a database together with all references required to restart the resumable operation.

I've found that the log can be truncated (by a log backup) even if you have not paused the resumable rebuild.

As for Standard Edition... As far as I can tell, you don't stand a chance!

J. Mini
  • 1,161
  • 8
  • 32
1

From docs

I believe you can reduce the impact of that index maintenance using the bulk-logged recovery model:

If the database is set to the simple or bulk-logged recovery model, some index DDL operations are minimally logged whether the operation is executed offline or online. The minimally logged index operations are as follows:

  • CREATE INDEX operations (including indexed views).
  • ALTER INDEX REBUILD or DBCC DBREINDEX operation.

Index build operations use minimal logging, but might be delayed when there's a concurrently executing backup. This delay is caused by the synchronization requirements of minimally logged buffer pool pages when using the simple or bulk-logged recovery model.

About reaching the log file limit, you could create more than one log file for that maintenance, disposing the extra log file afterwards. Maybe that's the reason you couldn't find that problem when googling it.

Guess

It's possible that the limit of 2TB only exists if you use the UNLIMITED size, but if you specified 3TB it might create it (can't test it though).

Ronaldo
  • 6,017
  • 2
  • 13
  • 43