5

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.
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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.