2

How much log index rebuild will generate.I remebered reading rebuild index should generate same amount of log file as table size.But my tests are showing otherwise.We need this estimate since we are trying to build azure database indexes and it has a limitation of max 2 GB.

My database is in full recovery model.

Table Size:

enter image description here

log size:

enter image description here

from the pictures you can see log generation was very less with online index rebuild operation.Can some one correct me if i am missing anything

1
  • I did backed up database to disk=nul and also taken log backup immediately after db creation to make sure database is not in pseudo full recovery model Dec 30, 2014 at 14:58

1 Answer 1

5
+50

If you are rebuilding a clustered index, you are essentially rebuilding the table, but a non-clustered index is just a subset of the table (usually). If you use the SORT_IN_TEMPDB option, you will offload some of the logging to the tempdb transaction log. Version will also matter, in 2005 online rebuilding is minimally logged but was changed back to fully logged in 2008. You might also want to take a look at this article which might be useful in deciding whether to rebuild or reorganize with the log size in mind.

----Update----

Using Shanky's test setup I performed the same steps, but I added a few extra size checks. Before the rebuild, I perform a log backup and check the log space and utilization as well as the records in the transaction log for my index. Then I do the rebuild test and recheck the log size and the number of log records:

backup log IndexLogging to disk = 'NUL'
--check log space/usage
dbcc sqlperf (logspace)
--check log records for the index, exclude the where for the total number of logs generated
SELECT LogRecordsGenerated = COUNT(*) 
FROM sys.fn_dblog(NULL, NULL) 
WHERE AllocUnitName = 'dbo.Logging.IX_LOGGING' 
--Perform your rebuild operation and then recheck the same parameters

I tried this about a dozen times and each time I get the same results, the DMV is only telling you for the transaction, but there are several nested transactions that are spawned as a result of the log_test transaction that don't show up including the allocation of extents and pages and the insertion of the data. If you look at the actual contents of the log file, you can see how the offline rebuild is very efficient. It just allocates the pages/extents, formats them and sets them and then deallocates the old ones. An online rebuild is doing a lot more work since it has to keep the index available while it rebuilds. This is probably more evident in the locking: offline it locks the entire object but online it has to go page by page, key by key. You can take a look for yourself to compare:

SELECT
OPERATION,
Context,
[Transaction Name],
[AllocUnitName],
[AllocUnitId],
[Page ID],
Description,
[Lock Information], [Transaction ID]
FROM FN_DBLOG(NULL,NULL)

You can reduce the log generated by temporarily switching to BULK_LOGGED while your index maintenance runs, but you lose the ability to do point in time recovery for that period of time. Apparently it also does not reduce your log backup.

--More info on log usage from DBCC SQLPERF (logspace) and SELECT LogRecordsGenerated = COUNT(*) FROM sys.fn_dblog(NULL, NULL) WHERE AllocUnitName = 'dbo.Logging.IX_LOGGING' to show actual log sizes, note the index is only 648KB

This is the log after populating it with data:

Database Name Log Size (MB) Log Space Used (%) Status

IndexLogging 14.99219 70.89956 0

LogRecordsGenerated

10903

Here is the log after a log backup:

Database Name Log Size (MB) Log Space Used (%) Status

IndexLogging 14.99219 5.100313 0

LogRecordsGenerated

0

Log after log backup and then Online rebuild:

Database Name Log Size (MB) Log Space Used (%) Status

IndexLogging 14.99219 22.642 0

LogRecordsGenerated

11160

Log after log backup and then offline rebuild:

Database Name Log Size (MB) Log Space Used (%) Status

IndexLogging 14.99219 10.79338 0

LogRecordsGenerated

140

3
  • Thanks for the suggestions and articles.i am rebuilding clustered index but my log size is not as much as table size.i am using only with option with online =on Dec 30, 2014 at 14:53
  • @TheGameiswar I added a little more detail about the log size. How are you getting the size you listed? If you are looking at just the rebuild transaction, that is why you aren't seeing as much log as I noted. According to this the transaction log is limited to 10GB but a single transaction is limited to 2GB. It appears since a rebuild spawns other transactions for the DDL then it should work but you'd have to test to verify. Jan 2, 2015 at 20:27
  • In my tests, rolling back the source transaction does cause the rollback of the other transactions that the rebuild creates. When I took a look at the size, there was not much difference since the rollback doesn't generate that many more records. It appears to just continue the original transaction and reallocates and populates the original pages and then spawns a transaction to deallocate the pages from the rebuild. When it is done, you are left with the same fragmentation on the same pages you started with. Jan 5, 2015 at 15:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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