SQLServer日志配置講解

大家知道SQLServer日志配置嗎?SQL Server數據庫引擎在內部將每一物理日志文件分成多個虛擬日志文件,這樣日志管理系統可以輕松的跟蹤那些部分是可以被重用的。事務日志文件根據下面的公式決定生成多少個VLFs,不管是自動增長還是手動增長：

Up to 1MB2 VLFs, each roughly 1/2 of the total size

1MB to 64MB4 VLFs, each roughly 1/4 of the total size

64MB to 1GB8 VLFs, each roughly 1/8 of the total size

More than 1GB16 VLFs, each roughly 1/16 of the total size

舉個例子，如果創建一個8GB的事務日志文件，那麼會得到16個VLF,每個大約512MB.如果日志一次性增長4GB,那麼我們會得到另外16個VLF,每個大約256MB，整個文件具有32個VLF.

一般最好的做法是設置日志的自動增長而不是默認的10%,這樣你可以更好控制日志由於zero-initializing操作導致的暫停。比方說，你創建一個256MB的事務日志，並設置自動增長到32MB，然後將日志增長到16GB的穩態大小。根據上述公式，這將導致你的交易記錄有4000多的VLF。

這許多的VLF很可能會對需要事務日志的操作（如崩潰恢復，清除日志，日志備份，事務復制，數據庫恢復）產生性能問題。這種情況被稱為有VLF碎片。一般來說任何數量的VLF超過一千元左右將是有問題的，需要加以解決（我曾經聽說過的最多的是154萬的VLF在超過1TB的大小事務日志！）。

太多的VLFs可能會導致一些操作在處理日志的時候遇到性能問題(比如崩潰恢復，清除日志，日志備份，事務復制，數據庫恢復)。這種情況被稱為VLF碎片。一般來說超過1000的VLF是有問題的，需要加以解決（我曾經聽說過的1TB的事務日志文件有超過154W的VLF）.

查詢VLFs的數量可以使用undocumented（絕對安全）的DBCC LOGINFO命令。輸出的行數就是VLF在事務日志中的數量。如果你覺得VLF太多，可以用下面的方式減少：

1. 清除日志（比如通過日志備份等等截斷日志）

2. 手動收縮日志文件

3. 重復步驟1和2，直到日志達到小尺寸（在繁忙的生產系統可能會比較麻煩）

4. 手動將日志增長到期望的大小，比如8GB這樣VLFS單個VLF不超過0.5GB.

你可以閱讀更多有關VLF碎片問題並且如何解決：

· Microsoft KB article that advises reducing VLF numbers

· Can log files growth affect DML?

· 8 steps to better transaction log throughput

Tempdb

Tempdb日志配置應該跟其他數據庫一樣，而且也會像其他數據庫一樣自動增長。但是它有一些潛在的因素會導致問題。當一個SQL Server實例重新啟動後，tempdb數據庫的數據和日志文件將恢復到初始文件設置的大小，而其他數據庫會保持當前大小不變。

這種行為意味著當tmpdb已經增長到合適大小，你必須使用Alter database設置日志文件的固定大小，否則重啟之後日志文件需要從設置的初始值增長到合適大小。每當日志增長，新的空間必須要做零初始化並且將導致日志記錄暫停，這個會影響性能。所以如果你沒有正確的管理tempdb日志文件的大小，那麼在實例重啟之後你將會付出性能的損失。

定期日志收縮

很多時候聽到人們說他們在發現由於一些些普通的操作（例如每周一次的數據導入）導致數據庫日志增長時，會做定期的收縮，這個操作我是不建議的。

正如我上面所解釋的，每當事務日志增長或自動增長，都會因為日志文件zero-initialize動作導致停頓。如果事務日志文件經常需要增長到大小x,那麼這意味著你的應用將會在日志增長到X的過程中遭受性能影響。

如果你的交易記錄不斷增加X大小，不要管它！主動將其設置為大小X，按照我們上面提到的方法管理VLF,因為這個大小是數據庫正常操作需要的。

多個事務日志文件

一個數據庫創建多個日志文件對性能不會有提升。當前的日志空間不足時，可能需要增加第二個日志文件。如果不增加第二個日志文件可以通過將數據庫的恢復模式修改為Simple並且執行檢查點(這會打破記錄備份鏈)。

經常有人問我是否要除去第二個日志文件還是將它保留在原處。答案是盡快將其移除。

雖然第二個日志文件不會導致性能問題，但是可能影響災難恢復。如果你的數據庫由於某種原因被損壞，你需要從頭恢復。還原的第一步是當數據和日志文件不存在的時候創建這兩個文件。當你創建數據文件的時候可以啟用instantfile initialization參數，這個選項會跳過zero-initialization，但是這個參數不適用於日志文件。這意味著使用完整備份恢復需要創建所有的日志文件（或在事務日志備份恢復期間）並且做zero-initialize。如果創建了第二個日志文件但是沒有刪除，zero-initialize過程增加了總的停機時間。雖然這不會導致性能問題，但是影響了服務器的可用性。

從數據庫快照恢復

最後一個問題其實是在SQL Server中的bug。如果你使用一個數據庫快照，以此來快速恢復到某個時間已知點從而避免還原備份（稱為從快照恢復），那麼你就可以節省大量的時間。然而，有一個很大的缺點。

當數據庫從數據庫快照恢復，事務日志重新創建了兩個0.25MB的VLF。這意味著你需要手動調整日志文件大小到最佳值（或它會??自動增長），否則又會導致前面提到的零初始化並且導致日志暫停的問題，顯然這不是我們期望的。

總結：

從這篇文章可以看出，有很多事情可以導致事務日志性能問題，進而導致整個庫性能問題。可以利用我們上面提到的方法設置你的日志，這樣會擁有健康的日志。除此之外，你還需要監視事務日志，比如由於自動增長和　過度的讀取和寫入ＩＯ延遲。這些會在將來的文章中解釋。

相信大家已經了解SQLServer日志配置吧!感謝大家對我們網站的支持!

相關推薦：

SQLServer中merge函數簡介

想了解更多關於sqlserver教程，請點擊精品學習網編程開發欄目！！