探查 SQL Server 虛擬記錄檔

SQL Server 把交易記錄檔劃分為多個VLFVirtual Log File），即虛擬記錄檔。

650) this.width=650;" border="0" src="http://www.bkjia.com/uploads/allimg/131228/230034D28-0.png" alt="112954536.png" />

一、VLF的4種狀態

1、active。表示VLF中存在活動的事務即未完成的事務）。

2、recoverable。表示VLF中的事務全部已經完成，但是某些操作例如資料庫鏡像、複製等）還需要用到這些資料，因此不可以被覆蓋。

3、reusable。表示VLF中的資料已經不需要了，可以被覆蓋。

4、unused。表示VLF從未被使用。

二、簡單復原模式時的VLF

1、檔案的初始狀態

　　測試資料庫名為db01，復原模式設為簡單。mdf與ldf檔案初始狀態如下：

650) this.width=650;" border="0" src="http://www.bkjia.com/uploads/allimg/131228/23003453W-1.png" alt="103942391.png" />

2、填充資料

　　運行以下指令碼，填充大量資料：

create table testtable (UserID varchar(100),pwd varchar(200)) declare @i int declare @d1 datetime set @i=0 -- truncate TABLE [testtable] insert into testtable select 'a','b' while @i<18 begin set @d1 = getdate() insert into [testtable] select UserID+cast(floor(rand()*1000) as nvarchar(50)), cast(floor(rand()*100000) as nvarchar(50))+UserID+cast(floor(rand()*100) as nvarchar(50)) from [testtable] set @i=@i+1 -- print '第' + Cast(@i as varchar(50)) + '圈，用時：' + Cast(datediff(ms,@d1,getdate()) as varchar(50)) + 'ms' end

3、檢查磁碟增長

　　運行指令碼後，LDF檔案由最初的1MB增長到53MB。

　　開啟“報表”－－“標準報表”－－“磁碟使用方式”。

650) this.width=650;" border="0" src="http://www.bkjia.com/uploads/allimg/131228/230034MX-2.png" alt="114633236.png" />

4、檢查VLF

　　執行dbcc loginfo，檢查VLF使用方式。

650) this.width=650;" border="0" src="http://www.bkjia.com/uploads/allimg/131228/2300344L2-3.png" alt="104823245.png" />

　　上表中的FileID=2表示資料庫的第2個檔案，對於db01這個測試庫，第2個檔案就是唯一的LDF檔案。FileSize表示VLF的大小。FSeqNo表示VLF的序號，如果為零則表示這個VLF未被使用。CreateLSN為零，表示建立資料庫時就同時建立了這些VLF；不為零則表示在LSN產生時才建立這個VLF。

　　Status列表示VLF的狀態，如果為0則表示這個VLF為reusable或者unused，如果為2則表示這個VLF為active或者recoverable。

　　可以通過sys.databases 系統檢視表查看交易記錄不能被截斷的原因。例如：

select log_reuse_wait , log_reuse_wait_desc from sys.databases where name='db01'

650) this.width=650;" border="0" src="http://www.bkjia.com/uploads/allimg/131228/2300345b0-4.png" alt="132718935.png" />

5、查看VLF中的LSN

　　通過dbcc loginfo，可以查到Status=2的最小的FSeqNo=1865，那麼可以通過以下語句查詢這個VLF中的LSN。

select * from ::fn_dblog(1865000000000000001 ,1866000000000000000 )

650) this.width=650;" border="0" src="http://www.bkjia.com/uploads/allimg/131228/2300346007-5.png" alt="143215130.png" />

　　第1列顯示的16進位LSN不能直接使用，需要換算成正常的10進位LSN。以第一行為例，第1段為VLF序號，749H換算成10進位為1865；第2段為日誌區塊的第1個扇區編號，6EH換算成10個字元的10進位為0000000110；第3段為該扇區內的流水號，1H換算成4個字元的10進位為0001。因此，正常的LSN為186500000001100001。

6、checkpoint的影響

　　從上表可以看出，存在一些Status=2的VLF。如果資料緩衝區仍有一些“髒資料”未回寫到mdf檔案，可以手動執行checkpoint，使“髒資料”立即進行回寫。前面的例子中，從sys.databases 系統檢視表可以看到有一個事務還未完成，導致checkpoint延遲）

　　說明：在生產環境中，即使在簡單復原模式，如果有個事務很早就Begin Transaction，而忘記被Commit/Rollback，結果可能看到一個很大的記錄檔。

　　說明：何時執行checkpoint，請參考《SQL Server 何時將“髒頁”回寫到硬碟》 http://jimshu.blog.51cto.com/3171847/1202748

　　再執行dbcc loginfo，檢查VLF使用方式。可見絕大多數的VLF已經Status=0。

　　再檢查“磁碟使用方式”。

650) this.width=650;" border="0" src="http://www.bkjia.com/uploads/allimg/131228/2300341958-6.png" alt="105135233.png" />

7、收縮記錄檔

　　在簡單復原模式時，自動或手動checkpoint操作，以及對資料庫做完全備份都會將recoverable狀態的VLF標記成Status=0，這些VLF可以被不斷重用。如果沒有大量的事務導致checkpoint延遲或者“髒資料”回寫延遲，LDF檔案就不需要增長。

　　收縮記錄檔時，資料庫引擎會檢查VLF的狀態，將Status=0即reusable和unused）的VLF所佔用的空間釋放出來，然後LDF再收縮它的邊界。

三、完整復原模式時的VLF

　　在完整復原模式時，對資料庫執行完全備份時不會影響VLF。只有執行交易記錄備份之後，VLF才會將recoverable狀態的VLF被標記成reusable狀態如果有記錄傳送、鏡像等操作，會對些操作造成限制）。

　　如果VLF不能被重用，將導致LDF檔案持續增長。執行交易記錄備份後，VLF的狀態被標記為Status=0，才可以被重用。

　　為了避免LDF過度增長，應當增加交易記錄備份的頻率。

四、參考資料

1、《交易記錄 (SQL Server)》

http://msdn.microsoft.com/zh-cn/library/ms190925.aspx#Truncation

2、《資料庫檢查點 (SQL Server)》

http://msdn.microsoft.com/zh-cn/library/ms189573.aspx

3、《簡介資料庫記錄檔的增長》

http://blogs.msdn.com/b/apgcdsd/archive/2011/12/30/10251946.aspx

本文出自 “我們一起追過的MSSQL” 部落格，請務必保留此出處http://jimshu.blog.51cto.com/3171847/1174474