A couple of days ago, I encountered an interesting case with a customer concerning a rebuild index operation on a datawarehouse environment. Let me introduce the situation: a “usual DBA day” with an almost usual error message found in your dedicated mailbox: “The transaction log for database ‘xxx’ is full”. After checking the concerned database, I notice that its transaction log has grown up and has fulfilled the entire volume. In the same time, I also identify the root cause of our problem: an index rebuild operation performed last night that concerns a big index (approximately 20 GB in size) on a fact table. On top of all, the size of the transaction log before raising the error message was 60 GB.
As you know, on datawarehouse environment, the database recovery model is usually configured either to SIMPLE or BULK_LOGGED to minimize write operations of bulk activity and of course the concerned database meets this requirement. According to the Microsoft document we could expect to get minimally logged records for index rebuild operations (ALTER INEX REBUILD) regardless the offline / online mode used to rebuild the index. So why the transaction log has grown heavily in this case?
To get a response we have first to take a look at the rebuild index tool used by my customer: the OLA script with INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE values for FragmentationHigh parameter. Don’t worry OLA scripts work perfectly and the truth is out there In the context of my customer, rebuild indexes online was permitted because the edition of the concerned SQL Server instance was Enterprise and this is precisely where we have to investigate here.
Let me demonstrate with a pretty simple example. On my lab environment I have a SQL Server 2014 instance with Enterprise edition. This instance hosts the well-known AdventureWorks2012 database with a dbo.bigTransactionHistory_rs1 table (this table is derived from the original script provided by Adam Machanic).
Here the current size of the AdventureWorks2012 database:
and here the size of the dbo.bigTransactionHistory_rs1 table:
Total used size: 1.1 GB
Because we are in SIMPLE recovery model, I will momentary disable the checkpoint process in order to have time to get log records inside the transaction log by using the traceflag 3505
dbcc traceon(3505, -1);
Case 1: ALTER REBUID INDEX OFFLINE
Let’s check the size of transaction log of the AdventureWorks2012 database
Case 2: ALTER REBUID INDEX ONLINE
Let’s check again the size of the transaction log of the AdventureWorks2012 database:
It is clear that we have an obvious difference of size concerning the transaction log for each operation.
Stay curious and let’s have a look deeper at the records written inside the transaction log for each mode by using the undocumented function sys.fn_dblog() as follows:
As expected we may notice a lot of records with index rebuild online operation comparing to the index rebuild offline operation (x21)
Let’s continue looking at the operations performed by SQL Server during the index rebuild operation in both cases:
The above picture is very interesting because we may again see an obvious difference between each mode. For example, if we consider the operations performed in the second case (on the right, some of them doesn’t concern bulk activity as LOP_MIGRATE_LOCKS, LOP_DELETE_ROWS, LOP_DELETE_SPLITS, LOP_MODIFY_COLUMS an unknown allocation unit, which probably concerns the new structure. At this point I can’t confirm it (I don’t show here all details o these operations. I let you see by yourself). Furthermore, in the first case (on the left), the majority of operations concerns only LOP_MODIFY_OPERATION on the PFS page (context).
Does it mean that the online mode doesn’t use minimaly mechanism for the whole rebuild process? I retrieved an interesting response from this Microsoft KB which confirms my suspicion.
Online Index Rebuild is a fully logged operation on SQL Server 2008 and later versions, whereas it is minimally-logged in SQL Server 2005. The change was made to ensure data integrity and more robust restore capabilities.
However I guess we don’t have the same behavior than the FULL recovery model here. Indeed, there still exists a difference between SIMPLE / BULK_LOGGED and FULL recovery models in term of amount of log records generated. Here a picture of the transaction log size after rebuilding the big index online in full recovery model in my case:
Ouch! 230MB (SIMPLE / BULK-LOGGED) vs 7GB (FULL). It is clear that using FULL recovery model with rebuild index online operations will have a huge impact on the transaction log compared to the SIMPLE / BULK-LOGGED recovery model. So the solution in my case consisted in switching to offline mode or at least reducing the online operation for the concerned index.