19

An "ALTER INDEX ALL REBUILD" operation on SQL Server 2012 failed because the transaction log ran out of space. The indexes have never been reorganized or rebuilt, so fragmentation is over 80% on nearly all of them.

The DB uses simple recovery model. I assumed that following each index operation performed by the "ALL" form of the command, the transaction log data would be flushed prior to the next index rebuild. Is that how it actually works, or are the index rebuilds logged as if they are part of a single transaction?

In other words, could I reduce transaction log growth by writing a script to perform each rebuild individually? Are there any other factors to consider?

2
  • 3
    Barring explicit data to the contrary, I would assume that a specific SQL command would be considered a single atomic transaction by the DB engine. In this case, you can fairly easily test the theory. Take your largest index, and try to just rebuild that. If that succeeds, then it's reasonable to assume that the log is accumulating information from multiple rebuilds. If it fails, then you need to add space for the log (as you've got a problem either way), or you need to try to reorganize that index, instead of rebuilding (if you can't boost space for the t-log).
    – RDFozz
    Jul 10, 2017 at 18:38
  • Yes, that thought occurred to me just as I finished typing this (rubber duck effect), but I thought it would be best to get confirmation, and to leave it up for others who might think the same way. I don't want to experiment with this environment, so I'll probably end up adding space to the logs either way. Jul 10, 2017 at 19:35

6 Answers 6

17

I assumed that following each index operation performed by the "ALL" form of the command, the transaction log data would be flushed prior to the next index rebuild. Is that how it actually works, or are the index rebuilds logged as if they are part of a single transaction?

1) Log flushing: the SIMPLE recovery model does not clear the log after every transaction, but at checkpoints. (link for more info)

2a) REBUILD ALL: yes, REBUILD ALL works as a single transaction. The index rebuilds within have their own transactions, but the overall operation isn't fully committed until the end. So yes, you might limit log file growth by rebuilding individual indexes (and possibly issuing CHECKPOINT commands).

2b) Proof! Here, have a demo script. (Built in 2016 dev) First, set up a test db, with table and indexes:

USE master
GO

CREATE DATABASE Test_RebuildLog
GO

ALTER DATABASE Test_RebuildLog
SET RECOVERY SIMPLE
GO

USE Test_RebuildLog
GO

CREATE TABLE IndexTest
(ID int identity(1,1),
a char(1),
b char(1))

CREATE CLUSTERED INDEX CIX_IndexTest_ID ON IndexTest(ID)
CREATE INDEX IX_IndexTest_a ON IndexTest(a)
CREATE INDEX IX_IndexTest_b ON IndexTest(b)

INSERT IndexTest
(a,b)
VALUES ('a','b'),('z','y'),('s','r')

Now you can compare log activity between REBUILD ALL and rebuilding individually

CHECKPOINT
GO
ALTER INDEX ALL ON IndexTest REBUILD

SELECT *
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation = 'LOP_COMMIT_XACT'
OR Operation = 'LOP_BEGIN_XACT'
GO

CHECKPOINT
GO
ALTER INDEX CIX_IndexTest_ID ON IndexTest REBUILD
ALTER INDEX IX_IndexTest_a ON IndexTest REBUILD
ALTER INDEX IX_IndexTest_b ON IndexTest REBUILD

SELECT *
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation = 'LOP_COMMIT_XACT'
OR Operation = 'LOP_BEGIN_XACT'
GO

Note how the first open transaction (Transaction ID 0000:000002fa for me) isn't committed until the end of the REBUILD ALL, but for the index-by-index rebuilds, they are successively committed.

2
  • 1
    Wow, thanks for the really detailed response! That's a great way to see what's happening under the hood, so to speak. Jul 11, 2017 at 0:55
  • Nicely explained. Jul 11, 2017 at 11:14
4

As it stands, this is single transaction.

3
  • 6
    Welcome to DBA.SE! In general, the best answers are not simple assertions, but are backed up by information from documentation or articles, or (often even better) personal experience proving out the stated answer. Can you expand on your answer, to provide that sort of backing?
    – RDFozz
    Jul 10, 2017 at 20:09
  • 2
    @RDFozz Fair comment, but did you look at Pedro's profile? Access to source code can probably be considered more authoritative than personal experience or documentation. :-) Jul 10, 2017 at 20:40
  • 5
    @AaronBertrand - I confess, I did not. I would certainly think being part of the SQL Server team would indeed qualify. Still, it's worth referencing that in the answer. +1, in any case.
    – RDFozz
    Jul 10, 2017 at 20:45
3

The question is trivial for an offline rebuild. Of course is a single transaction. Imagine the havoc that would ensue if the operation split each index into its own transaction, as it would have to release the locks when committing and then re-acquire them. While the critical table SCH-M lock was released, indexes may be dropped and new indexes could be created, how would the statement handle such cases? Not to mention that table may be dropped, and even re-created between the two transactions! Including the case when the table gets dropped and a different table gets created with the same object id (yes, it can happen)...

What if you augment the question to say what happens if the index rebuild is an online rebuild? Is it a single transaction or many? The answer is complex, as there are actually several internal transactions involved. However, the key point is that there is an overall arching transaction that spans the entire operation (the ALTER statement) and this pins the log in place (cannot truncate), thus the operation needs to be accordingly planned to allow for ~1.6x data size for FULL recovery mode, or 0.2x data size for BULK_LOGGED/SIMPLE mode. See linked paper for more details.

You can argue that why doesn't the offline build employ the same internal transactions the online mode does, and split the operation? The issues I mentioned about table being altered/dropped between the individual index operations (ie. table 'schema stability') would still require that there is an encompassing transaction that holds a SCH-S on the table for the entire duration of the statement. As this transaction must hold the SCH-S also during recovery, it must be logged, and as such there will be a BEGIN XACT log record that will pin the log and prevent truncation for the whole duration of the statement. I know this particular issue was being addressed in SQL 2016-2017 time frame (because of SQL Azure log size issues), but I'm not sure what progress was made. Looks like is in preview now: Resumable Online Index Rebuild is in public preview for SQL Server 2017 CTP 2.0.

1

The earlier response by Remus that online indexing requires 1.6x the index size under FULL recovery mode is not correct. The proportion of transaction logging space required to rebuild an index online under FULL can be much higher and we have observed many times the size of the index, especially when the index being rebuilt is compressed as the transaction logging is not compressed. This alone should make it clear that transaction logging during an online rebuild under FULL can be at least a few times the size of the index. Add in tlog record overhead which isn't fully documented by Microsoft but is often estimated at 60 bytes per row and the proportionate size of logging during an online index rebuild under full recovery can be many times the size of the index being rebuild, especially if the index is compressed

0

Yes, I had this same problem with a very large table. Whenever I issued ALTER INDEX ALL, transaction log would grow a lot, but if issued ALTER INDEX individually, the log space usage would be smaller.

-1

Rdfozz is correct that's the best way to decide whether your largest index can be rebuilt based on current storage. Just run dm_exec_requests while the operation is happening (or SQL Profiler) to see if all indexes are being rebuilt. I would also consider changing recovery model to bulk logged. This is what I do and there are transaction log backup's during the window still. See below article https://technet.microsoft.com/en-us/library/ms191484(v=sql.105).aspx

2
  • 2
    Note that the OP stated their DB is already using the SIMPLE recovery model; this only keeps transactions in the log long enough for the transactions to complete. There would be no improvement if they switched to bulk-logged.
    – RDFozz
    Jul 10, 2017 at 20:06
  • Your quite right. My apologies.
    – ADTJOB
    Jul 10, 2017 at 20:32

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.