原文: 簡(jiǎn)介數(shù)據(jù)庫(kù)日志文件的增長(zhǎng)

轉(zhuǎn)自： http://blogs.msdn.com/b/apgcdsd/archive/2011/12/30/10251946.aspx

我的數(shù)據(jù)庫(kù)只有10GB，為什么我的日志文件有40GB，而且還在增長(zhǎng)？

2.???????我無(wú)法對(duì)數(shù)據(jù)庫(kù)進(jìn)行更改操作了。報(bào)錯(cuò)說(shuō)我的日志空間已滿。

3.???????我數(shù)據(jù)庫(kù)的恢復(fù)模式明明是Simple，為什么還有日志，還那么大？

4.???????我的數(shù)據(jù)庫(kù)一直處于恢復(fù)狀態(tài)，其他應(yīng)用程序都報(bào)連接出錯(cuò)。

歸根結(jié)底一句話，我數(shù)據(jù)庫(kù)的日志的增長(zhǎng)遠(yuǎn)遠(yuǎn)超過(guò)我的預(yù)期，而導(dǎo)致我的業(yè)務(wù)受到影響。數(shù)據(jù)庫(kù)事務(wù)日志（Transaction Log），或簡(jiǎn)稱日志文件（Log）文件的 異常 增長(zhǎng) 有時(shí)候?qū)?shù)據(jù)庫(kù)的應(yīng)用的影響是致命的。

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

如果，你認(rèn)真的讀完這篇文章，你會(huì)發(fā)現(xiàn)這只不過(guò)是SQL Server的另一個(gè) 不恰當(dāng) 應(yīng)用的一個(gè)案例。

?

什么是日志文件（Transaction Log）

簡(jiǎn)而言之，日志文件通過(guò)一個(gè)簡(jiǎn)化的格式記錄了所有對(duì)數(shù)據(jù)庫(kù)的修改操作，包括Insert，?Update和Delete等能夠幫助你重現(xiàn)對(duì)數(shù)據(jù)庫(kù)內(nèi)容修改的操作。日志文件的后綴名為*.LDF。

數(shù)據(jù)文件?（*.mdf）Vs.?日志文件?（*.ldf）

有人本以為數(shù)據(jù)文件是存儲(chǔ)數(shù)據(jù)的“核心”文件，但是很詫異的發(fā)現(xiàn)，原來(lái)日志文件存儲(chǔ)了所有的數(shù)據(jù)修改。有些人會(huì)想到，通過(guò)日志文件，其實(shí)我就可以把我的數(shù)據(jù)庫(kù)恢復(fù)到某個(gè)特定時(shí)間點(diǎn)的狀態(tài)。這句話，在某些限制條件下是對(duì)的，將在下文進(jìn)行討論。

那么，數(shù)據(jù)文件和日志文件的關(guān)系是什么呢？他們?nèi)绾喂ぷ鳎?

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

Checkpoint是SQL Server的一個(gè)自動(dòng)的行為。 http://msdn.microsoft.com/en-us/library/ms188748.aspx ?所以，你的數(shù)據(jù)文件和日志文件的數(shù)據(jù)差異間隔會(huì)是兩個(gè)Checkpoint之間的時(shí)間差。Checkpoint本身也會(huì)被寫入日志文件。如果在某些情況下，Checkpoint并沒(méi)有如預(yù)期那樣短時(shí)間中發(fā)生，數(shù)據(jù)差異間隔會(huì)更長(zhǎng)。

日志文件如何影響我數(shù)據(jù)庫(kù)的啟動(dòng)？

無(wú)論你的SQL Server啟動(dòng)，或者你將某數(shù)據(jù)庫(kù)重備份中恢復(fù)，或者其他的一些情況，總之在你的數(shù)據(jù)庫(kù)能夠被正常使用之前，你的數(shù)據(jù)庫(kù)都會(huì)進(jìn)入?Recovery的狀態(tài)。 http://msdn.microsoft.com/en-us/library/ms190442.aspx ?如果這一個(gè)步失敗，那么你的數(shù)據(jù)庫(kù)就會(huì)進(jìn)入Suspect狀態(tài)而無(wú)法正常使用。在一些特殊情況下，這個(gè)Recovery所花費(fèi)的時(shí)間會(huì)很長(zhǎng)。在數(shù)據(jù)庫(kù)進(jìn)入Online狀態(tài)之前，我們都不能認(rèn)為SQL Server可以被正常使用了。

那么在Recovery中做了什么呢？

我們可以在你的SSMS中運(yùn)行如下語(yǔ)句。在運(yùn)行之前，你可以先按下Ctrl+T來(lái)把結(jié)果轉(zhuǎn)化成Plain Text格式。

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

在結(jié)果返回中，你可能會(huì)找到上面的這些信息。引號(hào)中為在你當(dāng)前數(shù)據(jù)庫(kù)實(shí)例下的各個(gè)數(shù)據(jù)庫(kù)名。

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

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

答案是：都在！

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

由于對(duì)于事物日志的修改要先于對(duì)于數(shù)據(jù)文件的修改，所以當(dāng)你的數(shù)據(jù)庫(kù)處于Recovery的狀態(tài)時(shí)，那么Transaction Log 就會(huì)從最近的一個(gè)Checkpoint 點(diǎn)開始做如下操作：

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

2.???????如果該操作已在在Transaction Log中，而不在Data File之中，并且如果它使用顯式的Transaction操作符而且處于Rollback Transaction狀態(tài)，則會(huì)發(fā)生一次Rolled back操作，將Data File之中的相關(guān)數(shù)據(jù)修改回滾到Transaction發(fā)生之前。

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

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

5.???????如上步驟都完成后，SQL Server會(huì)對(duì)該數(shù)據(jù)庫(kù)做一個(gè)Checkpoint的標(biāo)識(shí)，并寫入Transaction Log，表示Data File和Transaction Log已經(jīng)同步。這表明了數(shù)據(jù)庫(kù)的Recovery完成。數(shù)據(jù)庫(kù)將進(jìn)入Online狀態(tài)，并被正常使用。如果你的數(shù)據(jù)庫(kù)在最近的一次Checkpoint到現(xiàn)今的修改操作足夠多，在Error Log中也會(huì)看到SQL Server用百分比標(biāo)識(shí)Recovery完成的進(jìn)展。

?

問(wèn)題：我的數(shù)據(jù)庫(kù)一直處于?In Recovery狀態(tài)，跑了好幾個(gè)小時(shí)了。

解決方案：好吧，這個(gè)說(shuō)明你的數(shù)據(jù)庫(kù)自從上一次的Checkpoint到你現(xiàn)在的這個(gè)點(diǎn)對(duì)該數(shù)據(jù)庫(kù)的修改足夠多。這種情況所對(duì)應(yīng)的癥狀是：

1．?你有個(gè)Begin很長(zhǎng)時(shí)間的Transaction，但是沒(méi)有Commit/Rollback。

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

2．?你的數(shù)據(jù)庫(kù)也許很長(zhǎng)時(shí)間沒(méi)有做Checkpoint了。

·?????????如果是這樣，建議你在Performance Monitor檢測(cè)一下Checkpoint的調(diào)用頻率。

問(wèn)題：我已經(jīng)理解了Recovery的工作模式，但是我真的很急，而且愿意承擔(dān)Data File和Transaction Log之間的差異。我只是想讓我的數(shù)據(jù)庫(kù)快點(diǎn)上線。

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

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

1．?設(shè)置單用戶，設(shè)置緊急狀態(tài)。

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

3．?重建Log

?????

?

如何維護(hù)事務(wù)日志（Transaction Log）

首先我們要理解SQL Server數(shù)據(jù)庫(kù)的恢復(fù)模式。

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

·?????????Full：完全記錄事物日志?？梢赃M(jìn)行日志備份。

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

·?????????Simple：無(wú)法進(jìn)行日志備份。意味著你并不在乎日志文件。但是，依然生成數(shù)據(jù)庫(kù)日志文件。

雖然，日志文件記錄了所有的數(shù)據(jù)庫(kù)的更改操作，但是日志文件并非無(wú)限增長(zhǎng)的，是因?yàn)镾QL Server會(huì)按照一定的規(guī)則重用Log Space。

1.???????首先，Log是順序記錄的。而來(lái)自不同Transaction的對(duì)于同一個(gè)數(shù)據(jù)庫(kù)的Log可能是交錯(cuò)出現(xiàn)。

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

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

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

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

c.???????滿足以上條件的Virtual Log Space被標(biāo)識(shí)為可重用。Full和Bulk-Logged恢復(fù)模式的數(shù)據(jù)庫(kù)在備份日志時(shí)標(biāo)識(shí)。Simple模式的數(shù)據(jù)庫(kù)在做Checkpoint時(shí)標(biāo)識(shí)可重用。

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

對(duì)于日志文件的重用性，通常會(huì)有以下誤區(qū)：

1．??Simple模式下沒(méi)有日志文件。

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

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

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

???????????????????????????????????????????????????????????????i.??????恢復(fù)?Full Backup2：0-250。

?????????????????????????????????????????????????????????????ii.??????加載/恢復(fù)Log Backup2: 201-300.?實(shí)際被恢復(fù)Log Backup2的LSN：251-300。

????????????????????????????????????????????????????????????iii.??????加載/恢復(fù)?Log Backup2: 301-400。

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

·?????????Log Backup Chain會(huì)在以下情況下被打破。

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

?????????????????????????????????????????????????????????????ii.??????恢復(fù)模式在Full/Bulk Logged和Simple之間進(jìn)行轉(zhuǎn)換。

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

·?????????通常，我們并不推薦使用Shrink的方式來(lái)進(jìn)行收縮。因?yàn)镮O對(duì)于SQL Server而言是個(gè)非常昂貴的操作。你通過(guò)Shrink的方式收縮的磁盤空間，會(huì)在下次再次被SQL Server重新申請(qǐng)。而且在很多時(shí)候，你使用Shrink能夠回收的磁盤空間并不那么理想。所以我們的目標(biāo)是提高文件的重用性。

所以，對(duì)于維護(hù)日志來(lái)說(shuō)主要需要注意的是以下幾個(gè)方面：

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

2．??Transaction盡可能短而快。避免長(zhǎng)時(shí)間開啟一個(gè)Transaction，或者Begin Transaction而忘記Commit/Rollback Transaction的事情發(fā)生。

問(wèn)題：我的日志文件很大，而且收縮不下來(lái)。

解決方案：

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

第一步，你需要查找的是，是否有個(gè)開了很長(zhǎng)時(shí)間的Transaction，但是沒(méi)有Commit/Rollback？可以通過(guò)以下語(yǔ)句查找該數(shù)據(jù)庫(kù)下，跑了最長(zhǎng)而沒(méi)有被提交的Transaction。

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

第二步，通過(guò) DBCC ?SQLperf ( Logspace ) 檢查L(zhǎng)og文件被使用的情況。

如果數(shù)據(jù)庫(kù)的Log Size很大，而Log Space Used(%)相對(duì)較小。那么說(shuō)明有足夠可以被收縮的空間。（但是當(dāng)Log Space Used很大時(shí)，也不代表無(wú)法收縮足夠的空間）

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

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

?

?

總結(jié)

其實(shí)如果有人問(wèn)我20GB的Transaction Log大不大。其實(shí)這個(gè)問(wèn)題只是個(gè)相對(duì)概念。理論上只要你有足夠的磁盤空間，日志文件可以足夠大。如果你有個(gè)400GB的數(shù)據(jù)庫(kù)，20GB的日志文件也在合理的范疇。如果你的數(shù)據(jù)庫(kù)只有5GB，那么你的應(yīng)用程序?qū)τ赥ransaction的使用和日志的維護(hù)一定是有問(wèn)題了。

簡(jiǎn)介數(shù)據(jù)庫(kù)日志文件的增長(zhǎng)