在大表上創建非聚集索引會填充事務日誌

使用 SQL Server 2016，我試圖在我的數據庫中的表上創建一個非聚集覆蓋索引，該表的結構為int, bigint, int, varchar(20). varchar(4000)大小約為 13,241,928 行。

create nonclustered index nix_TableName on Schema.TableName (
   Column2 asc, Column3, asc
) include (Column4, Column5) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

問題是事務日誌很快就會填滿，這些日誌位於分區為 50GB 的驅動器上。數據庫位於具有 3 個節點 DW1、DW2、DW3 的可用性組上。

在不崩潰的情況下創建此索引的最佳/實用方法是什麼？我可以像分批那樣限制索引創建嗎？有什麼我遺漏的技巧可以包含在我的create nonclustered index陳述中嗎？

我確實找到了這個文件。接下來我將嘗試其中的一些技巧。

表上已經有一個聚集列儲存索引。

此外檢查伺服器的活動監視器，我發現索引創建過程正在暫停。隨著wait_type存在CXPACKET。我沒有指定MAX_DOP.

據我所知，基礎表是列儲存索引這一事實在這裡並不太相關。您在問題中包含的連結文件已經為您提供了問題的標準修復。以下是評級為的可能修復列表

1. 增加您的日誌文件大小。如果您出於某種原因需要刪除並重新創建索引，這將對您有所幫助。
2. 將SORT_IN_TEMPDB選項設置為ON。我不知道這一點，但是當您在 tempdb 中有足夠的可用空間時，該選項會有所幫助。
3. 使用頁面壓縮建構索引。這可以減少寫入事務日誌的數據量，但 varchar(4000) 列可能會導致問題。
4. 使用您需要的索引創建表結構的副本，並將數據批量插入其中。這在生產中可能很難實現，並且總體上效率低下，但是您可以通過這種方式將索引建構拆分為單獨的事務。如果出現問題，只需刪除您創建的表的臨時副本。
5. 如果表是分區的，您可能能夠使用分區切換來一次建構一個分區的非聚集索引。
6. 暫時將您的恢復模式更改為批量記錄。這可能會導致各種問題，因此請確保您完全了解這樣做的所有後果。

我會為日誌驅動器增加儲存空間。

我猜你在那個數據庫上做維護。擁有更多空間可能會阻止索引重建失敗。

50Go 對於一個非常大的數據庫來說並不算多。由於儲存並不那麼昂貴，因此可能值得花費 $ 來避免因日誌文件已滿而導致數據庫停機的事件。

謝謝。

引用自：https://dba.stackexchange.com/questions/193852