SQL Server最小日志的操作

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

最小化日志記錄僅記錄恢復(fù)事務(wù)所需的信息，而不支持任意時(shí)間點(diǎn)恢復(fù)，也就是說(shuō)，在最小化日志記錄操作時(shí)，SQL Server也會(huì)記錄事務(wù)日志，但是僅記錄回滾事務(wù)所需的有限信息?！坝邢扌畔ⅰ笔侵?，僅把分配的頁(yè)面記錄在事務(wù)日志中，而沒(méi)有記錄這些頁(yè)面包含的實(shí)際數(shù)據(jù)，因此保持了較小的事務(wù)日志文件的大小。

一，最小日志操作

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

在最小化日志記錄期間執(zhí)行大容量數(shù)據(jù)插入，雖然數(shù)據(jù)插入不會(huì)記錄在事務(wù)日志中，但是，對(duì)于每次為表分配的區(qū)（8個(gè)物理地址連續(xù)的Page）都會(huì)記錄在事務(wù)日志中。不是所有的操作都能實(shí)現(xiàn)最小化日志記錄，最小化日志操作的類型：

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

SELECT INTO

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

有意思的是，TRUNCATE 并不是最小化日志記錄操作，在任何還原模式下，TRUNCATE 都完整記錄事務(wù)日志的，并能夠還原到任意時(shí)間點(diǎn)，不過(guò)TRUNCATE記錄日志的效率更高，采用deferred-drop 機(jī)制來(lái)記錄日志。

二，觸發(fā)最小日志的條件

測(cè)試用例的環(huán)境是SQL Server 2017版本，在 SIMPLE或BULK_LOGGED還原模式下做測(cè)試。

實(shí)際上，要在執(zhí)行大容量插入時(shí)實(shí)現(xiàn)最小化日志記錄，必須滿足五個(gè)條件：

數(shù)據(jù)庫(kù)處于SIMPLE或BULK_LOGGED還原模式

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

不是復(fù)制表

不是內(nèi)存優(yōu)化表

在滿足前四個(gè)條件的基礎(chǔ)上，有如下結(jié)論：

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

結(jié)論1：表沒(méi)有索引，Data Page執(zhí)行最小化日志記錄。

結(jié)論2：表沒(méi)有聚集索引，但是有非聚集索引，Data Page執(zhí)行最小化日志記錄。

當(dāng)表是空的時(shí)，Index Page執(zhí)行最小化日志記錄

當(dāng)表有數(shù)據(jù)時(shí)，Index Page執(zhí)行完整日志記錄

對(duì)于使用分Batch插入的情況，當(dāng)表是空的，對(duì)于第一個(gè)Batch插入，Data Page和Index Page都執(zhí)行最小化日志記錄；從第二個(gè)Batch開(kāi)始，Data Page執(zhí)行最小化日志記錄，而Index Page執(zhí)行完整日志記錄。

結(jié)論3：表有聚集索引

當(dāng)表有聚集索引，并且是空表時(shí)，Data Page和Index Page都執(zhí)行最小化日志記錄。

當(dāng)表有聚集索引，并且有數(shù)據(jù)時(shí)，Data Page和Index Page都執(zhí)行完整日志記錄。

對(duì)于使用分Batch插入的情況，當(dāng)表是空的，對(duì)于第一個(gè)Batch插入，Data Page和Index Page都執(zhí)行最小化日志記錄；從第二個(gè)Batch開(kāi)始，Data Page執(zhí)行最小化日志記錄，而Index Page執(zhí)行完整日志記錄。

結(jié)論4，從表中可以看出：

索引頁(yè)的分配都是Fully Logged，

堆表的數(shù)據(jù)頁(yè)更新都是Min Logged，

只有當(dāng)表是聚集索引時(shí)，數(shù)據(jù)頁(yè)的更新才是Fully Logged的，實(shí)際上，BTree表就是索引本身。

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

從上節(jié)中的結(jié)論4中知道，索引頁(yè)的分配都是Fully Logged，索引頁(yè)的回收（deallocation ）也都是Fully Logged。在特定的情況下，執(zhí)行CREATE INDEX、ALTER INDEX REBUILD 和 DROP INDEX能夠激發(fā)數(shù)據(jù)頁(yè)的最小化日志記錄，索引的重建（REBUILD）相當(dāng)于先刪除索引，再創(chuàng)建索引。

比如，創(chuàng)建索引相當(dāng)于向有數(shù)據(jù)的表中插入數(shù)據(jù)，索引頁(yè)是Full Logged，數(shù)據(jù)表根據(jù)結(jié)論4來(lái)判斷數(shù)據(jù)頁(yè)是Full Logged或Min Logged。

四，延遲刪除

對(duì)于TRUNCATE TABLE，概況來(lái)說(shuō)，是通過(guò)回收已分配的數(shù)據(jù)頁(yè)來(lái)移除數(shù)據(jù)，并且只把回收的數(shù)據(jù)頁(yè)記錄在事務(wù)日志中。

DROP TABLE 和 TRUNCATE TABLE 都是完整記錄日志的操作，不過(guò)日志不是立即創(chuàng)建，而是延遲記錄，這是由延遲刪除（deferred drop）的機(jī)制來(lái)實(shí)現(xiàn)的。當(dāng)一個(gè)表被 drop 或 truncate 時(shí)，屬于該表的所有數(shù)據(jù)頁(yè)都會(huì)被系統(tǒng)標(biāo)記為回收，并把標(biāo)記為回收的數(shù)據(jù)頁(yè)和區(qū)放置在延遲刪除隊(duì)列（deferred-drop queue）中，該數(shù)據(jù)頁(yè)或區(qū)實(shí)際上并沒(méi)有釋放，只是標(biāo)記為回收（deallocation ）。延遲刪除機(jī)制通過(guò)回收表的數(shù)據(jù)頁(yè)，從而模擬drop 或 truncate操作立即完成后的效果，這個(gè)過(guò)程僅僅產(chǎn)生很少的日志記錄。

但是延遲刪除的后臺(tái)處理程序（deferred-drop background task）每隔幾秒鐘就會(huì)執(zhí)行一次，并以小批量的方式回收放置在延遲刪除隊(duì)列（deferred-drop queue）中的所有Page和Extent，從而確保操作不會(huì)耗盡內(nèi)存?；厥湛臻g的操作是完全記錄日志的，不過(guò)，釋放一個(gè)充滿數(shù)據(jù)或索引記錄的頁(yè)面，并不會(huì)記錄個(gè)別數(shù)據(jù)行的刪除。相反，整個(gè)頁(yè)面只是在相關(guān)的PFS（Page Free Space）分配字節(jié)圖中標(biāo)記為已取消分配。

從SQL Server 2000 SP3開(kāi)始，執(zhí)行表的DROP或TRUNCATE時(shí)，只會(huì)看到一些正在生成的日志記錄。如果等待一分鐘左右，然后再次查看事務(wù)日志，您將看到deferred-drop操作已經(jīng)生成了成千上萬(wàn)的日志記錄，每個(gè)日志記錄都表示回收一個(gè)Page或Extent。