Sample Header Ad - 728x90

Can a 5 terabyte index be rebuilt online without worrying about the transaction log?

1 vote
2 answers
189 views
### Context Buried deep in [this documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-file-and-filegroup-options?view=sql-server-ver16#arguments) , 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.
Asked by J. Mini (1237 rep)
Dec 30, 2024, 10:36 PM
Last activity: Jan 2, 2025, 01:15 PM