ALTER是否使用简单的恢复模型使用更多的事务日志空间，而不是单独地重建每个索引？

Question

由于事务日志空间不足，Server 2012上的“ALL重建”操作失败。索引从未被重新组织或重建，因此几乎所有索引的碎片都超过80%。

DB使用简单的恢复模型。我假设在命令的"ALL“形式执行的每个索引操作之后，事务日志数据将在下一个索引重建之前被刷新。这是它的实际工作方式，还是索引重建被记录为它们是单个事务的一部分？

换句话说，我是否可以通过编写一个脚本来单独执行每个重建，从而减少事务日志的增长？还有其他因素要考虑吗？

Answer 1

我假设在命令的"ALL“形式执行的每个索引操作之后，事务日志数据将在下一个索引重建之前被刷新。这是它的实际工作方式，还是索引重建被记录为它们是单个事务的一部分？

1)日志刷新:简单的恢复模型并不是在每个事务之后清除日志，而是在检查点清除日志。(链接获取更多信息)

( 2a)重建所有:是的，将所有工作重建为一个单一事务。索引内部重建有它们自己的事务，但是直到结束时，整个操作才会完全提交。因此，您可以通过重建单个索引来限制日志文件的增长(并可能发出检查点命令)。

(B)证据！这里，有一个演示脚本。(建于2016 dev)首先，建立一个测试数据库，包含表和索引：

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

现在，您可以比较重建ALL和单独重建之间的日志活动。

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

注意第一个打开的事务(对我来说是000002fa的事务ID )是如何直到重建全部结束时才提交的，但是对于逐个索引的重建，它们是连续提交的。

Answer 2

就目前情况而言，这是单笔交易。

Answer 3

Remus早先的反应，即在线索引需要1.6倍的索引大小，在完全恢复模式下是不正确的。在FULL下在线重建索引所需的事务日志空间的比例可能要高得多，而且我们已经观察到索引大小的很多倍，特别是当正在重建的索引被压缩时，因为事务日志记录没有被压缩。仅这一点就可以清楚地表明，在FULL下进行联机重建期间的事务日志记录至少可以是索引大小的几倍。添加tlog记录开销，Microsoft没有完全记录，但通常估计为每行60个字节，并且在完全恢复下联机索引重建期间日志记录的比例大小可以是正在重建的索引大小的许多倍，特别是在索引被压缩时。