Online clustered index rebuild using more log space than expected

Question

I have a table TableA

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.

Answer 1

After contacting Paul Randal of SQLSkills he informed me that the reason for the high rates of transaction log usage is that in an ONLINE index re/build each row has it's own log record with all the overhead of a log record. An OFFLINE rebuild logs full 8kb page images, not row by row.

Related link provided by Paweł Tajs:

SQL Q&A: The Lore of Logs (also by Paul Randal)