Sql-server – Online clustered index rebuild using more log space than expected

clustered-indexmaintenancesql serversql-server-2012transaction-log

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.

Best Answer

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)

Related Question