[SQL]如何清除交易紀錄檔 ?

使用 DBCC LOGINFO 來觀察當使用 BACKUP LOG 指令,交易紀錄黨內的狀態。

其實這個已經不是甚麼新鮮的題目了,只是剛好有朋友有詢問到,因此也就很順口的要他去「備份交易紀錄檔」。但朋友是個開發人員,當他這樣跟他們 MIS 說明的時候,他們 MIS 就是不相信他所說的,因此就決定做個範例,來證明當復原模式是完整的時候,執行備份交易紀錄檔,就會清除交易紀錄了。

首先先去建立一個範例資料庫,因為交易紀錄檔設定太大,要塞好多測試資料才能看到效果,因此這個我將交易紀錄檔初始大小設定為 8MB ,每次成長也是 8MB。

接下來我們透過 DBCC LOGINFO 的指令來觀察交易紀錄檔,可以從下圖看出來,這個交易紀錄檔目前有切割成 4 個虛擬記錄檔 ( VLF - Virtual log file ) ,關於 VLF 的部分可以參考微軟的文件。我們可以看到只有第一個 VLF 的狀態是 2 ( 作用中 ) ,其他都是 0 ( 非使用中 )

因為當資料庫建立後,如果沒有執行過完整備份,那麼交易紀錄檔是不會一值成長,而使採用復原模式是簡單的方式去運作,因此這個我先進行一次完整資料庫備份

BACKUP DATABASE DEMO TO DISK='NUL'

 

接下來我們來建立一個範例資料表,填入一些資料,看看是否交易紀錄黨是否有變化

CREATE TABLE TESTDATA( F1 INT IDENTITY(1,1) , F2 NCHAR(1024) )
GO

 

透過以下的指令來塞入一些範例資料,讓交易紀錄檔有所成長

SET NOCOUNT ON
INSERT TESTDATA(F2) VALUES ( CAST(NEWID() AS NCHAR(1024)))
GO 10000
SET NOCOUNT OFF
CHECKPOINT

 

執行上上述指令之後,我們再來透過 DBCC LOGINFO 指令來查看交易紀錄檔的變化。從下面檔案中可以看到,原本四個 VLF 的交易紀錄檔,因為新增 10000 筆資料,造成交易紀錄檔塞滿了原本的 4 個 VLF,且成長了 12 個 VLF 的檔案,且其中的 15 個狀況是作用中的。

因此我們來試試看備份交易紀錄檔

BACKUP LOG DEMO TO DISK='NUL'

 

並且一樣透過 DBCC LOGINFO 來做觀察,從中可以看到,當我們還沒有下 BACKUP LOG 之前,交易紀錄檔內有很多的 VLF 都是作用中的,而下完備份交易紀錄檔之後,會將除了還有在使用的最後一個 VLF 外,其他的 Status 都設定為非使用中

因此這個時候我們再重複之前的填入資料的處理,看交易紀錄黨是否會循環使用

SET NOCOUNT ON
INSERT TESTDATA(F2) VALUES ( CAST(NEWID() AS NCHAR(1024)))
GO 10000
SET NOCOUNT OFF
CHECKPOINT

 

接著一樣還是使用 DBCC LOGINFO 來觀察,你可以從下圖中看到,交易紀錄檔會從原本的 FSeqNo 50 的那個 VLF 寫完之後,往下寫 FSeqNo 51 的 VLF ,而在那個寫滿之後,交易紀錄檔案並沒有再去長大,反而回頭去使用前面的 VLF 檔案

上述這個都是邏輯上的變化,但實際檔案是否也如同 DBCC LOGINFO 所列出的檔案大小呢 ? 因此我們也實際看一下交易記錄檔的實體大小,從下圖中可以看出,交易紀錄檔就如同 DBCC LOGINFO 中所揭示的檔案大小

希望可以透過這篇文章,可以幫助朋友,讓他可以拿去跟 MIS 說明清楚了。ㄙ