原文: 簡介數據庫日志文件的增長

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

我的數據庫只有10GB，為什么我的日志文件有40GB，而且還在增長？

2.???????我無法對數據庫進行更改操作了。報錯說我的日志空間已滿。

3.???????我數據庫的恢復模式明明是Simple，為什么還有日志，還那么大？

4.???????我的數據庫一直處于恢復狀態，其他應用程序都報連接出錯。

歸根結底一句話，我數據庫的日志的增長遠遠超過我的預期，而導致我的業務受到影響。數據庫事務日志（Transaction Log），或簡稱日志文件（Log）文件的 異常 增長 有時候對數據庫的應用的影響是致命的。

想象一下，在你的業務最繁忙的時候，你的數據庫由于日志文件占滿整個磁盤空間，而無法使用。有人說，很簡單啊！我把日志文件刪掉。如果你真的這樣做，你會發現數據庫日志文件正被SQL Server進程鎖定，而無法刪除。于是，你停止SQL Server服務，然后很開心的將Transaction Log文件刪除了，節省了大概200GB的空間。再于是，你很“傷心”的發現你的數據庫無法啟動，因為缺少日志文件。如果你在日志文件原有的路徑創建一個同名的日志文件，好吧，既然你那么堅決，SQL Server會告訴你日志文件格式已損壞。突然，你發現自己有個很好的習慣，當你刪除那個200GB的日志文件的時候，你只是把它扔到了垃圾箱，于是你把它找了回來，重新啟動你的SQL Server，很開心的發現，它成功的啟動了。于是你松了一口氣，泡上一杯咖啡，考慮是不是找你的Disk Vendor申請更多的磁盤空間。突然你的電話鈴聲大作，好多連接到你的數據庫的應用程序的管理人員都向你抱怨他們無法連接到數據庫。你打開SSMS，發覺那個數據庫一直處于“恢復中”（In Recovery）狀態???好像它永遠不會終止。好吧，你是不是有想砸機器的沖動了，并且想氣勢洶洶的告訴全世界，這是微軟SQL Server的另一個Bug？

如果，你認真的讀完這篇文章，你會發現這只不過是SQL Server的另一個 不恰當 應用的一個案例。

?

什么是日志文件（Transaction Log）

簡而言之，日志文件通過一個簡化的格式記錄了所有對數據庫的修改操作，包括Insert，?Update和Delete等能夠幫助你重現對數據庫內容修改的操作。日志文件的后綴名為*.LDF。

數據文件?（*.mdf）Vs.?日志文件?（*.ldf）

有人本以為數據文件是存儲數據的“核心”文件，但是很詫異的發現，原來日志文件存儲了所有的數據修改。有些人會想到，通過日志文件，其實我就可以把我的數據庫恢復到某個特定時間點的狀態。這句話，在某些限制條件下是對的，將在下文進行討論。

那么，數據文件和日志文件的關系是什么呢？他們如何工作？

SQL Server是一個很依賴于內存（Memory）使用的系統。任何一個對于數據的讀入/修改都是和內存進行交互。當一個修改操作發生時，修改的將是內存中所對應的在內存中的數據頁。這個操作將會 實時地 被寫入日志文件。但是，該修改被寫入數據文件（*.mdf）的時間，只有在以下三種情況下發生：（1）做Checkpoint 時（2）Lazy write運行時（3）Eager write運行時。Lazy write發生在有內存壓力時，而Eager write通常發生在bulk insert和select into操作時的。 這里只談比較普遍的checkpoint情況。

Checkpoint是SQL Server的一個自動的行為。 http://msdn.microsoft.com/en-us/library/ms188748.aspx ?所以，你的數據文件和日志文件的數據差異間隔會是兩個Checkpoint之間的時間差。Checkpoint本身也會被寫入日志文件。如果在某些情況下，Checkpoint并沒有如預期那樣短時間中發生，數據差異間隔會更長。

日志文件如何影響我數據庫的啟動？

無論你的SQL Server啟動，或者你將某數據庫重備份中恢復，或者其他的一些情況，總之在你的數據庫能夠被正常使用之前，你的數據庫都會進入?Recovery的狀態。 http://msdn.microsoft.com/en-us/library/ms190442.aspx ?如果這一個步失敗，那么你的數據庫就會進入Suspect狀態而無法正常使用。在一些特殊情況下，這個Recovery所花費的時間會很長。在數據庫進入Online狀態之前，我們都不能認為SQL Server可以被正常使用了。

那么在Recovery中做了什么呢？

我們可以在你的SSMS中運行如下語句。在運行之前，你可以先按下Ctrl+T來把結果轉化成Plain Text格式。

sp_readerrorlog 會返回自從最近一次SQL Server服務啟動，或者Error Log被回收開始的SQL Server的Error Log信息。這里有兩個詞匯可能會讓你感到疑惑。 首先，這里的Log和我們上文所說的日志文件（Transaction Log）中的Log不是一件事情。其次，所謂的Error Log，并不是說這里出現了Error。 SQL Server使用這個Error Log記錄了很多診斷信息。所以，你可以認為這只是一個普通的記錄了很多SQL Server相關信息的日志文件。

在結果返回中，你可能會找到上面的這些信息。引號中為在你當前數據庫實例下的各個數據庫名。

以數據庫‘CaseLync’為例。由于Error Log會記錄當前SQL Server實例下的所有數據庫的相關信息，所以你可能會看到和這個數據庫相關的日志被順序地分布在日志的不同地方。在上圖中，第一和第二行提及兩個詞：rolled forward和rolled back。這兩個詞和我們本文中所提及的Transaction Log息息相關。

在前文中，我們提到過數據修改的過程：先同步的被寫入Transaction Log，然后再Checkpoint發生的時候被同步到Data File里。那么，如果我的事物（Transaction）沒有被提交（Commit）或者回滾（Rollback），那么這個數據修改是否也在日志文件（Transaction Log）還是在數據文件里（Data File）？

答案是：都在！

SQL Server在記錄數據改變時，并不會區分該語句是否有顯示的進行事物操作（Begin Transaction，Commit / Rollback Transaction）,?或者該事務是否有完成。SQL Server會忠實地記錄所有的修改操作。而Begin Transaction和Commit / Rollback Transaction本身也是日志文件需要記錄的操作之一。

由于對于事物日志的修改要先于對于數據文件的修改，所以當你的數據庫處于Recovery的狀態時，那么Transaction Log 就會從最近的一個Checkpoint 點開始做如下操作：

1.???????如果該操作已在在Transaction Log中，而不在Data File之中，并且如果它使用顯式的Transaction操作符而且處于Commit狀態，則會發生一次Rolled Forward操作，將該操作同步到Data File之中。

2.???????如果該操作已在在Transaction Log中，而不在Data File之中，并且如果它使用顯式的Transaction操作符而且處于Rollback Transaction狀態，則會發生一次Rolled back操作，將Data File之中的相關數據修改回滾到Transaction發生之前。

3.???????如果該操作已在在Transaction Log中，而不在Data File之中，并且如果它使用顯式的Transaction在日志文件中即沒有Commit操作，也沒有Rollback操作，則會發生一次Rolled back操作，將Data File之中的相關數據修改回滾到Transaction發生之前。

4.???????如果該操作已在在Transaction Log中，而不在Data File之中，并且如果它沒有使用顯式的Transaction操作符（Begin Transaction??,?則被認為是一個Rolled Forward操作。

5.???????如上步驟都完成后，SQL Server會對該數據庫做一個Checkpoint的標識，并寫入Transaction Log，表示Data File和Transaction Log已經同步。這表明了數據庫的Recovery完成。數據庫將進入Online狀態，并被正常使用。如果你的數據庫在最近的一次Checkpoint到現今的修改操作足夠多，在Error Log中也會看到SQL Server用百分比標識Recovery完成的進展。

?

問題：我的數據庫一直處于?In Recovery狀態，跑了好幾個小時了。

解決方案：好吧，這個說明你的數據庫自從上一次的Checkpoint到你現在的這個點對該數據庫的修改足夠多。這種情況所對應的癥狀是：

1．?你有個Begin很長時間的Transaction，但是沒有Commit/Rollback。

·?????????如果是這樣，慢慢等吧。

2．?你的數據庫也許很長時間沒有做Checkpoint了。

·?????????如果是這樣，建議你在Performance Monitor檢測一下Checkpoint的調用頻率。

問題：我已經理解了Recovery的工作模式，但是我真的很急，而且愿意承擔Data File和Transaction Log之間的差異。我只是想讓我的數據庫快點上線。

解決方案：第一件你需要考慮的是三思你是否確實要這樣做。雖然也許Data File與Transaction Log之間的差異并非那么多，但是你可能會在你的Data File里留下沒有被Commit的數據，從而在邏輯上影響你應用程序的使用。

如果你真的決定那么做，那么遵循以下步驟：

1．?設置單用戶，設置緊急狀態。

2．?獲取Transaction Log的物理名和邏輯名

3．?重建Log

?????

?

如何維護事務日志（Transaction Log）

首先我們要理解SQL Server數據庫的恢復模式。

SQL Server的數據庫恢復模式分為3種 http://msdn.microsoft.com/en-us/library/ms189275.aspx ?：

·?????????Full：完全記錄事物日志。可以進行日志備份。

·?????????Bulk-Logged：使用于批量操作的數據庫。以更壓縮的方式處理日志。可以進行日志備份。

·?????????Simple：無法進行日志備份。意味著你并不在乎日志文件。但是，依然生成數據庫日志文件。

雖然，日志文件記錄了所有的數據庫的更改操作，但是日志文件并非無限增長的，是因為SQL Server會按照一定的規則重用Log Space。

1.???????首先，Log是順序記錄的。而來自不同Transaction的對于同一個數據庫的Log可能是交錯出現。

2.???????Transaction Log能夠申請/釋放的空間以Virtual Log File(VLF)為單位。

3.???????在同時滿足以下條件的前提下，Log Space可以被重用，即被覆蓋

a.???????最近一次Checkpoint之前。意味著Log File和Data File已經同步。

b.??????最早一個Begin而沒有Commit/Rollback的Transaction之前的Virtual Log File(VLF)。

c.???????滿足以上條件的Virtual Log Space被標識為可重用。Full和Bulk-Logged恢復模式的數據庫在備份日志時標識。Simple模式的數據庫在做Checkpoint時標識可重用。

4.???????Transaction Log到文件尾以后，如果文件頭的Virtual Log File(VLF)可以被重用，則會回溯到文件頭。

對于日志文件的重用性，通常會有以下誤區：

1．??Simple模式下沒有日志文件。

·?????????其實，任何模式下都有日志文件產生。只不過，Simple模式下通過Checkpoint自動地將可以重用的Virtual Log File(VLF)標識為可重用。如果有個很早就Begin Transaction，而忘記被Commit/Rollback，你依然可能看到一個很大的日志文件。

2．??完整備份（Full Backup）會打斷日志備份鏈（Log Backup Chain）。

·?????????這個問題源自于Log Shipping：在一臺做Log Shipping的Primary?機器上，是否可以做Full Backup？答案是可以的。假設你在做初始化的Full Backup1時的LSN是0-100,?其后周期性的Log Backup1, Log Backup2, Log Backup3的LSN分別是101-200，?201-300，301-400。而在做第二個Log Backup時，你在該機器上做了一次Full Backup2的LSN為：0-250。則當你從Full Backup2?開始做Restore過程是這樣的：

???????????????????????????????????????????????????????????????i.??????恢復?Full Backup2：0-250。

?????????????????????????????????????????????????????????????ii.??????加載/恢復Log Backup2: 201-300.?實際被恢復Log Backup2的LSN：251-300。

????????????????????????????????????????????????????????????iii.??????加載/恢復?Log Backup2: 301-400。

·?????????所以Log Backup2其實包含了完整的日志，但是在上述恢復的例子中，將從251開始恢復。

·?????????Log Backup Chain會在以下情況下被打破。

???????????????????????????????????????????????????????????????i.??????第一次做Full Backup。

?????????????????????????????????????????????????????????????ii.??????恢復模式在Full/Bulk Logged和Simple之間進行轉換。

3．??如果文件很大（Data File或者Transaction Log File）,我通過Shrink來收縮空間。

·?????????通常，我們并不推薦使用Shrink的方式來進行收縮。因為IO對于SQL Server而言是個非常昂貴的操作。你通過Shrink的方式收縮的磁盤空間，會在下次再次被SQL Server重新申請。而且在很多時候，你使用Shrink能夠回收的磁盤空間并不那么理想。所以我們的目標是提高文件的重用性。

所以，對于維護日志來說主要需要注意的是以下幾個方面：

1．??對于Full和Bulk-Logged，定期做日志備份。（在此之前最起碼有1個完整備份。）兩次備份的間隔是你能夠容忍數據丟失的時間跨度。

2．??Transaction盡可能短而快。避免長時間開啟一個Transaction，或者Begin Transaction而忘記Commit/Rollback Transaction的事情發生。

問題：我的日志文件很大，而且收縮不下來。

解決方案：

首先，我們并不贊成通過自動/定期收縮的方式來控制文件的大小。我們建議通過建立良好的維護計劃來提高日志文件空間的重用性而達到同樣的目的。當然，如果你想把日志文件收縮（Shrink）到一個合理的程度，你可以參考如下步驟。

第一步，你需要查找的是，是否有個開了很長時間的Transaction，但是沒有Commit/Rollback？可以通過以下語句查找該數據庫下，跑了最長而沒有被提交的Transaction。

如果有的話，你要么通知那個應用程序的管理人員來Commit/Rollback?這個Transaction。要么在SQL Server這邊粗暴的殺死這個Session。下例中，1234為執行這個Transaction的Session ID（SPID）。

第二步，通過 DBCC ?SQLperf ( Logspace ) 檢查Log文件被使用的情況。

如果數據庫的Log Size很大，而Log Space Used(%)相對較小。那么說明有足夠可以被收縮的空間。（但是當Log Space Used很大時，也不代表無法收縮足夠的空間）

第三步，通過 dbcc ?loginfo檢查VLF的使用的情況。

在這里值得關注的是Status列。0 –?可以被Log所使用的VLF。?2-?已被使用，而且無法重用的VLF。通過備份Log的方式，將Status為2的行變成0。此時該空間已可被重用。如果，你真的想要進行日志收縮，可以在這步之后進行。則OS可以回收從最后一個0到最近一個2行的空間。（當前的Status=2的行標識當前的Log記錄位置）

?

?

總結

其實如果有人問我20GB的Transaction Log大不大。其實這個問題只是個相對概念。理論上只要你有足夠的磁盤空間，日志文件可以足夠大。如果你有個400GB的數據庫，20GB的日志文件也在合理的范疇。如果你的數據庫只有5GB，那么你的應用程序對于Transaction的使用和日志的維護一定是有問題了。

簡介數據庫日志文件的增長