I have a table
ID PK BIGINT TrxID BIGINT CardBalance INT AccountBalance INT
SP_SPACEUSED 'TableA' output:
name rows reserved data index_size unused TableA 904054184 55547120 KB 34661408 KB 19963544 KB 922168 KB
So the data size is about 33 GB. There is a clustered index on TrxID. There is one nonclustered index, which comes to about 19GB.
My transaction log file size is about 250 GB.
DBCC SQLPERF('logspace') shows < 1% usage.
When I run:
ALTER INDEX CIX_TableA_TrxID ON dbo.TableA REBUILD WITH (ONLINE = ON)
The transaction log hits 100% after about 20 minutes and then continues to grow.
Is there a reliable way to calculate how much log space is required for this operation?
Also this seems like a lot of space to use in the log file, does this sound right?
- This database is in an Availability Group (so using the full recovery model) with 1 other asynchronous replica.
- This is a test database, so there aren't any other transactions running at the same time as the the reindex.