SQL Server最小日志的操作

SQL Server之所以記錄事務日志，首要目的是為了把失敗或取消的操作還原到最原始的狀態，但是，并不是所有的操作都需要完全記錄事務日志，比如，在一個空表上放置排他鎖，把大量的數據插入到該空表中。即使插入操作在任意時刻失敗，只需要把清空表，就可以把表還原，根本不需要記錄插入的詳細數據。在表上放置排他鎖的目的，是為了阻止其他人更新該表，當插入失敗時，只需要清空表就還原到最原始的狀態。

最小化日志記錄僅記錄恢復事務所需的信息，而不支持任意時間點恢復，也就是說，在最小化日志記錄操作時，SQL Server也會記錄事務日志，但是僅記錄回滾事務所需的有限信息。“有限信息”是指，僅把分配的頁面記錄在事務日志中，而沒有記錄這些頁面包含的實際數據，因此保持了較小的事務日志文件的大小。

一，最小日志操作

在FULL還原模式下，所有的大容量操作都會完全記錄事務日志，在進行大容量數據插入時，最小化日志記錄更有效率，減少了事務日志空間在大容量操作時暴增的可能性，但是，如果在最小化日志記錄生效時數據庫已損壞或丟失，那么無法把數據庫恢復到故障點。

在最小化日志記錄期間執行大容量數據插入，雖然數據插入不會記錄在事務日志中，但是，對于每次為表分配的區（8個物理地址連續的Page）都會記錄在事務日志中。不是所有的操作都能實現最小化日志記錄，最小化日志操作的類型：

大容量導入操作(Bulk Import Operations)包括 BULK INSERT、BCP和 INSERT SELECT

SELECT INTO

索引操作：CREATE INDEX、ALTER INDEX REBUILD、DROP INDEX

有意思的是，TRUNCATE 并不是最小化日志記錄操作，在任何還原模式下，TRUNCATE 都完整記錄事務日志的，并能夠還原到任意時間點，不過TRUNCATE記錄日志的效率更高，采用deferred-drop 機制來記錄日志。

二，觸發最小日志的條件

測試用例的環境是SQL Server 2017版本，在 SIMPLE或BULK_LOGGED還原模式下做測試。

實際上，要在執行大容量插入時實現最小化日志記錄，必須滿足五個條件：

數據庫處于SIMPLE或BULK_LOGGED還原模式

表級鎖定，推薦使用表 hint 顯式上鎖：with(tablock)

不是復制表

不是內存優化表

在滿足前四個條件的基礎上，有如下結論：

一個表是否可以進行最小化日志記錄還取決于該表是否已建立索引，如果是，則取決于該表是否為空。

結論1：表沒有索引，Data Page執行最小化日志記錄。

結論2：表沒有聚集索引，但是有非聚集索引，Data Page執行最小化日志記錄。

當表是空的時，Index Page執行最小化日志記錄

當表有數據時，Index Page執行完整日志記錄

對于使用分Batch插入的情況，當表是空的，對于第一個Batch插入，Data Page和Index Page都執行最小化日志記錄；從第二個Batch開始，Data Page執行最小化日志記錄，而Index Page執行完整日志記錄。

結論3：表有聚集索引

當表有聚集索引，并且是空表時，Data Page和Index Page都執行最小化日志記錄。

當表有聚集索引，并且有數據時，Data Page和Index Page都執行完整日志記錄。

對于使用分Batch插入的情況，當表是空的，對于第一個Batch插入，Data Page和Index Page都執行最小化日志記錄；從第二個Batch開始，Data Page執行最小化日志記錄，而Index Page執行完整日志記錄。

結論4，從表中可以看出：

索引頁的分配都是Fully Logged，

堆表的數據頁更新都是Min Logged，

只有當表是聚集索引時，數據頁的更新才是Fully Logged的，實際上，BTree表就是索引本身。

三，索引操作中的最小化日志

從上節中的結論4中知道，索引頁的分配都是Fully Logged，索引頁的回收（deallocation ）也都是Fully Logged。在特定的情況下，執行CREATE INDEX、ALTER INDEX REBUILD 和 DROP INDEX能夠激發數據頁的最小化日志記錄，索引的重建（REBUILD）相當于先刪除索引，再創建索引。

比如，創建索引相當于向有數據的表中插入數據，索引頁是Full Logged，數據表根據結論4來判斷數據頁是Full Logged或Min Logged。

四，延遲刪除

對于TRUNCATE TABLE，概況來說，是通過回收已分配的數據頁來移除數據，并且只把回收的數據頁記錄在事務日志中。

DROP TABLE 和 TRUNCATE TABLE 都是完整記錄日志的操作，不過日志不是立即創建，而是延遲記錄，這是由延遲刪除（deferred drop）的機制來實現的。當一個表被 drop 或 truncate 時，屬于該表的所有數據頁都會被系統標記為回收，并把標記為回收的數據頁和區放置在延遲刪除隊列（deferred-drop queue）中，該數據頁或區實際上并沒有釋放，只是標記為回收（deallocation ）。延遲刪除機制通過回收表的數據頁，從而模擬drop 或 truncate操作立即完成后的效果，這個過程僅僅產生很少的日志記錄。

但是延遲刪除的后臺處理程序（deferred-drop background task）每隔幾秒鐘就會執行一次，并以小批量的方式回收放置在延遲刪除隊列（deferred-drop queue）中的所有Page和Extent，從而確保操作不會耗盡內存。回收空間的操作是完全記錄日志的，不過，釋放一個充滿數據或索引記錄的頁面，并不會記錄個別數據行的刪除。相反，整個頁面只是在相關的PFS（Page Free Space）分配字節圖中標記為已取消分配。

從SQL Server 2000 SP3開始，執行表的DROP或TRUNCATE時，只會看到一些正在生成的日志記錄。如果等待一分鐘左右，然后再次查看事務日志，您將看到deferred-drop操作已經生成了成千上萬的日志記錄，每個日志記錄都表示回收一個Page或Extent。