在這篇文章里，我想詳細談下為什么你要避免事務日志（Transaction Log）上的自動增長操作（Auto Growth Operations）。很多運行的數據庫服務器，對于事務日志，用的都是默認的日志文件大小和自動增長設置。人們有時會很依賴自動增長機制，因為它們剛好能正常工作。當然，如果它正常工作的話，你不必太關注它，但很快你會發現會有問題出現。

只依賴于事務日志的自動增長機制總不是個好主意。首先它會導致嚴重的日志碎片（Log Fragmentation），在SQL Server啟動期間，在你數據庫上執行崩潰恢復（Crash Recovery）時會有很大的負面影響。另外，在你數據庫里寫入事務需要等待，只要事務日志觸發了自動增長機制。

當事務日志的自動增長機制發生時，SQL Server總要零初始化新塊，這個會在文件末尾加上。這和你的SQL Server實例是否用即時文件初始化（Instant File Initialization）特權——事務日志總會零初始化。這上面的原因非常明顯：當SQL Server在過去已經完成事務日志的環繞式處理（wrap-around），崩潰恢復（Crash Recovery）需要知道在哪里停。

零初始化的問題是會占用更多的時間（取決與你的自動增長率，還有你的存儲速度）。在此期間沒有別的事務可以寫事務日志記錄到事務日志。在事務日志管理器上會有閂鎖造成的阻塞。因此你的寫入事務會進入掛起狀態（直到它們獲得需要的閂鎖），它們就等啊，等啊，等啊，直到你的事務日志自動增長完成。讓我們用一個簡單的例子演示下。

首先我為這個演示創建一個新的數據庫。對于這個數據庫，這里我不用默認的設置，對于事務日志，我指定了10GB的自動增長系數。這個的確是個不好的做法，但我只是用它來展示這個設置的副作用。請不要在你的生產數據庫里使用這個錯誤配置！！！

 1 -- Create a new database with 10 GB Auto Growth for the Transaction Log 2 CREATE DATABASE AutoGrowthTransactionLog ON PRIMARY  3 ( 4     NAME = N'AutoGrowthTransactionLog',  5     FILENAME = N'C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/AutoGrowthTransactionLog.mdf', 6     SIZE = 5120KB,  7     FILEGROWTH = 1024KB 8 ) 9 LOG ON 10 (11     NAME = N'AutoGrowthTransactionLog_log',12     FILENAME = N'C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/AutoGrowthTransactionLog_log.ldf',13     SIZE = 1024KB,14     FILEGROWTH = 10240000KB -- 10 GB Auto Growth!15 )16 GO

下一步里我在數據庫里創建2個表。第1個表我通過插入一些日志來快速填充我的事務日志。在事務日志自動增長階段，我們在第2個表里插入新的記錄來證明這個事務會被自動增長機制阻塞。

 1 -- Create a new table, every records needs a page of 8kb 2 CREATE TABLE Chunk 3 ( 4     Col1 INT IDENTITY PRIMARY KEY, 5     Col2 CHAR(8000) 6 ) 7 GO 8  9 -- Another simple table10 CREATE TABLE Foo11 (    12     Bar INT NOT NULL13 )14 GO

現在我們已經創建了必須的數據庫對象，因次我可以通過新的沒有立即提交的事務來填充事務日志：

1 -- Begin a new transaction, that blocks the 1st VLF in the Transaction Log2 BEGIN TRANSACTION3 INSERT INTO Chunk VALUES (REPLICATE('x', 8000))4 GO

因為我們現在有了進行中，沒提交的事務，SQL Server不能重用那部分事務日志，即這個事務存儲的事務日志。它們有需要回滾的可能。因此現在我通過不同的會話插入66條其他記錄來填充事務日志：

1 INSERT INTO AutoGrowthTransactionLog.dbo.Chunk VALUES (REPLICATE('x', 8000))2 GO 66

最后在第一個會話里提交我們的事務：

1 COMMIT

這意味著在我們面前有一個幾乎滿的的事務日志，我們可以通過DBCC LOGINFO來驗證：

1 DBCC LOGINFO

現在當我們往表里插入兮的記錄時，事務日志已經沒有可用空間了，SQL Server進入事務日志的自動增長。

1 -- This statement will trigger the Auto Growth mechanism!2 INSERT INTO Chunk VALUES (REPLICATE('x', 8000))3 GO

在自動增長期間的同時，為了監控發生了什么，我們可以在SSMS里打開新的一個會話窗口，嘗試在第2個表插入另外的記錄——表Foo：

1 -- This statement is now blocked by the Auto Growth mechanism.2 INSERT INTO Foo VALUES (1)3 GO

這個SQL 語句會阻塞，因為事務要寫入事務日志記錄的事務日志，當前不可用。為了進一步分析這個阻塞情形，你可以打開第3個會話窗口，執行下列2個SQL語句：

1 -- Analyze the blocking situation2 SELECT wait_type, * FROM sys.dm_exec_requests3 WHERE session_id IN (54, 55)4 5 SELECT wait_type, * FROM sys.dm_os_waiting_tasks6 WHERE session_id IN (54, 55)7 GO

（額，俺本機測試失敗………………）

從代碼里可以看到，我用2個DMVsys.dm_exec_requests 和 sys.dm_os_waiting_tasks對2個會話都進行了跟蹤——觸發自動增長的會話，和被自動增長機制阻塞的會話。在這里，觸發自動增長的會話里有所謂的搶占等待類型（Preemptive Wait Type）——PREEMPTIVE_OS_WRITEFILEGATHER。搶占等待類型是由SQL Server返回的等待類型，當SQL Server 執行一個WIN32 API函數在調度機制之外時。這里自動增長是通過WriteFileGather的WIN32 API函數完成的。

INSERT語句嘗試在Foo表里插入新的記錄出現LATCH_EX等待類型。如你從DMVsys.dm_os_waiting_tasks 里的resource_description列所見，在SQL Server的日志管理器上需要獲得閂鎖。你可以通過查詢DMVsys.dm_os_latch_stats 限制lactch class為LOG_MANAGER再次確認。在那個特定閂鎖上你會看到一些等待。那個閂鎖是事務獲取的，由事務日志的自動增長觸發，只要這個閂鎖要獲得，每個其他寫事務都會被阻塞。因此在系統上有大量等待時間時，這暗示這在事務日志里當前有自動增長問題需要處理。

希望我已經用這個日志說服你，依賴于事務日志的自動增長機制并不是最好的解決方案。用這個簡單的例子可以看到，在你數據庫里每個被自動增長操作阻塞的寫入事務會發生阻塞，這肯定會傷及你數據庫的吞吐量和擴展性。為了保證你有很好的事務日志性能，你可以最佳想實踐下這個文章。

感謝關注!