SQLServer中的事務日志管理(7/9)：處理日志過度增長_MySQL

當一切正常時，沒有必要特別留意什么是事務日志，它是如何工作的。你只要確保每個數據庫都有正確的備份。當出現問題時，事務日志的理解對于采取修正操作是重要的，尤其在需要緊急恢復數據庫到指定點時。這系列文章會告訴你每個DBA應該知道的具體細節。

這篇文章會列出導致事務日志過度增長的常見的問題和錯誤管理形式，包括：

在完整恢復模式里，沒有進行日志備份

進行索引維護

長時間運行或未提交的事務阻止事務日志里空間重用

當然，如果增長沒檢查，日志文件會擴展直到吞沒所有可用磁盤空間或日志文件的最大大小，在這個時候你會收到該死的9002錯誤（事務日志已滿），數據庫會變成只讀。這篇文章會談到處理日志不斷增長和9002錯誤的正確方法，還會解釋下通常建議截斷或收縮事務日志為什么是危險的。

最后，我們會談下保證你日志文件是平穩和可預見增長的措施，還有日志碎片的最小化問題。在忙碌的數據庫里，大型的事務日志會是一個簡單的生活事實，如果管理妥當的話，這并不是件壞事，即使大部分時間日志文件空間不可用。

日志大小和增長

任何時候日志文件需要增長，額外的空間被分配，這個空間平均分到VLS里，基于被分配空間數。

例如，日志文件默認會有2MB的初始大小，10%的自動增長率（來自model數據庫的配置）。這就是說，日志文件開始至少會很小的增長，因此會有大量的小VLF。

當我們在很大的塊上分配額外空間時，例如當一次操作初始16GB的大小，結果事務日志會有很小數量的VLF。

太高數量的小VLF，這樣的情況稱為日志文件碎片，會影響到性能，尤其在故障恢復，還原和備份，特別是日志備份。換句話說，它會影響讀取日志文件的操作性能。我們會在第8篇詳細討論這個問題。

事務日志VLF——太多還是太少？

SQL Server MVP的Kimberly Tripp在他的文章里討論了VLF大小的影響，并提供了如何恰當管理VLF大小的指導——事務日志VLF—太多還是太少？

相反，如果日志文件只有幾個很大的VLF，我們有長時間占用大塊日志的風險。每個VLF都有很大數量的日志記錄，SQL Server不能截斷VLF直到它沒有包含活動日志。這個情況下截斷會因某些原因延遲（在缺少日志空間重用部分會詳細談到），這會導致日志的快速增長。例如，我們假設每個VLF是1GB大小且日志滿了。你進行了一次日志備份，但是所有的VLF包含活動日志的一部分，SQL Server不能截斷日志。它沒有別的選擇只能增加更多VLF，如果日志的增長率設置為同等大小，那么日志增長會很快，直到有VLF變成可截斷。

因此，正確設置日志初始大小非常重要，那它的增長才會是合適的大小步驟，最小化日志碎片也避免了過快增長。

正確設置初始大小且可控制它的增長的第2個原因是：對于日志文件，每個增長是相對昂貴的操作。數據和日志文件增長超時是正常的。SQL Server可以優化增加新數據文件和擴展現有數據文件的過程，通過即時文件初始化（instant file initialization）（在SQL Server 2005引入，允許在磁盤上分配空間給數據文件，而不需要進行填零）。遺憾的是，對于日志文件是不一樣的，對于日志文件創建或增長的空間分配，還是需要初始化且填零。

為什么事務日志不能使用即時初始化？

進一步關注事務日志填零，看下Paul Randal的文章：http://sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-24-Why-cant-the-transaction-log-use-instant-initialization.aspx

事務日志不斷增長的診斷

如果你經歷事務日志的不可控增長，它由于要么是活動日志頻率太高，要么是有因素阻止日志文件里的空間重用，或者兩者都有。

如果增長的主要原因是活動日志過多，你要檢查下是否可以避免這個活動，例如調整處理大容量數據和索引維護的數據庫模式，這樣的話這些操作不會完整記錄（例如針對這些操作使用大容量日志恢復模式）。但是，如果日志備份里包含有任何的最小化日志操作，大容量操作會立即阻止數據庫到時間點的恢復（可以閱讀下第6篇文章來獲得更多詳細信息）。如果這是不可接受的，你必須直接接受大日志的事實，根據具體情況計劃它的增長和管理（例如日志備份頻率），在接下來的妥當的日志管理會介紹。

如果增長原因是缺少日志空間的重用，你要找出什么阻止這個重用并采取措施來修正這個問題。

日志過度增長：索引維護操作

索引維護操作是個很常見的導致事務日志使用率過度和增長的原因，尤其數據庫運行在完整恢復模式里。進行索引維護需要日志空間量取決于下列因素：

重建還是重組——通常索引重建在日志里會使用更多的空間

恢復模式——如果時間點恢復的風險已理解且可接受的，那么索引重建可以臨時通過切換到大容量恢復模式里的最小化日志。但索引重組始終是完整記錄的。

索引重建

當重建索引時，不管在線還是離線，使用ALTER INDEX REBUILD，或是已經廢棄的SQL Server 2000里的DBCC DBREINDEX，SQL Server新創建一個索引的副本，然后一旦重建完整，刪除老的副本（這是為什么你至少需要數據文件里索引大小一樣的可用空間）

日志記錄和在線索引重建

在SQL Server 2008和后續版本，在線索引重建是完整日志操作，在SQL Server 2005里是最小化日志。因此，在后續SQL Server版本上進行這樣的操作本質上需要更多的事務日志空間，可以看下：https://support.microsoft.com/zh-cn/kb/2407439 還有Kalen Delaney的日志，對于完整和大容量日志2個恢復模式，驗證下在在線和離線索引重建期間的日志記錄。

在完整恢復模式里，索引重建可以是非常占資源的操作，需要事務日志里的很多空間。在簡單或大容量日志恢復模式里，重建索引是最小化日志操作，這意味著只有分配被記錄，實際的頁并沒改變，因此通過這個操作減少了日志空間量。

如果你切換到簡單模式進行索引重建，LSN鏈會立即中斷。你只能恢復你的數據庫到剛才事務日志備份里的包含的時間點。為了重新開始日志鏈，你需要切換回完整恢復模式并立即進行一次完整或差異數據庫備份。

如果你切換到大容量日志模式（看下第6篇），LSN鏈還是連續的，但還會影響到進行時間點的恢復，因為包含最小化日志操作的日志備份不能用來恢復到時間點。如果能恢復到時間點的要求是至高無上的，那么索引重建或任何最小化日志操作不要使用大容量日志恢復模式。除非在數據庫里沒有同時發生的用戶活動，你可以使用。不然的話，在可能的情況下考慮在完整恢復模式里進行索引重建。

如果使用的是大容量日志模式，盡可能使時間點恢復的時間最小，這樣可以最小化暴露數據丟失風險。為了做到這一點，在完整模式里進行日志備份，切換到大容量日志，進行索引重建，然后切換回完整進行另一個日志備份。

最后一個重點要記住的是ALTER INDEX REBUILD操作是在一個單獨事務里。如果索引很大，事務的執行時間會很長，在期間，這會阻止日志里的空間重用。這就是說，即使你在簡單模式里重建索引，你也要想到自檢查點（CHECKPOINT）操作后日志應該保持很小，重建是最小化日志，在劇烈的重建期間，日志文件還會快速擴展。

索引重組

和重建索引相比，使用ALTER INDEX REORGANIZE或者SQL Server里的DBCC INDEXDEFRAG(已廢棄)重組（碎片整理）索引都是完整記錄操作，不管是任何恢復模式，因此實際的頁修改總被記錄。但是，通常索引重組比索引重建需要更少的日志空間，盡管這是索引里降低碎片的一個功能；比起輕度碎片，重度碎片索引會需要更多的日志空間來重組。

另外，ALTER INDEX REORGANIZE操作是通過多個更短的事務完成的。因此，當與定期的日志備份相結合（或在簡單恢復模式里）時，在此操作期間，日志空間可以被重用，因此要求操作期間日志空間最小化。

例如，對于重建操作，重建20GB的索引會需要超過20GB的空間，因為它發生在一個單獨的事務里。但是，重組20GB的索引會需要更少的空間，因為在重組里每個頁分配修改是個單獨的事務，因此日志記錄可以用定期日志備份截斷，讓日式空間可以重用。

控制日志過度措施

如果你的組織對任何潛在數據丟失不能容忍的，那么你沒有選擇，只能讓所有的數據庫運行在完整恢復模式里，并且妥當計劃你的日志大小和增長。因此索引重建是作為一個單獨線程發生的，日志至少會和你重建的索引一樣的大小。如剛才所說，索引重組會需要更少的空間，且允許在操作期間通過日志備份來截斷日志。這樣的話，為了同時避免日志暴漲，可行的話，你可以用日志重組。

如果你的SLA和操作級別協議（Operational Level Agreements（OLAs））允許一些潛在的數據丟失，那么在索引重建前l切換到大容量日志恢復模式可以為重建索引最小化空間需求量。但是，要在最小化數據丟失的方式下進行，例如已經討論確認過了。

不管使用的恢復模式，你可以通過重組索引而不是重建索引來在日志上最小化索引維護操作的影響。可以的話，可以看下微軟的指導方針，為了最小化索引維護操作的影響，對于絕大數情況，并不是所有情況，決定什么時候進行索引重建，什么時候進行索引重組（查看索引重組和重建）。他們也聲明：對于碎片級別大于5%且小于30%，你應該重組索引，對于碎片級別大于30%，你應該重建它。

但是，在索引維護期間，在保護日志過度增長里，最有效的武器是維護那些真正需要的索引。使用SSMS維護計劃向導，索引維護是個孤注一擲的操作：要么重建（或重組）數據庫里（維護計劃里的所有數據庫）的所有索引，要么全不維護。一個更好的方法是使用sys.dm_db_index_physical_stats的DMV來看下碎片程度根據需要來決定索引重建/重組策略。

Ola Hallengren的免費維護腳本

Ola Hallengren提供一個綜合的免費維護工具，它展示了如何使用sys.dm_db_index_physical_stats進行索引分析來進行智能維護，它可以用來代替SSMS里向導創建的數據庫維護計劃（https://ola.hallengren.com/）。

但是最好的方法，是計劃只維護那些可以在查詢上提供真正持久影響的索引。邏輯碎片（在亂序中的索引頁）挫敗了SQL Server的預讀機制（https://msdn.microsoft.com/zh-cn/library/ms191475%28v=sql.105%29.aspx），且使在磁盤上讀取連續頁I/O-效率更低。但是，這只真正影響從磁盤的大范圍掃描。即使對非常大碎片的索引，如果你不掃描表，重建或重組索引不會提高性能。降低頁深度（通過頁分裂或刪除造成的很多缺口）會帶來更多的頁占用磁盤空間，且在內存里，會需要更多的I/O帶寬來傳輸數據。再說一次，這個碎片格式不會真正影響不頻繁修改的索引，因此重建它們不會有幫助。

計劃索引維護前，問下自己什么性能標準從維護受益？它會大會減少I/O？它會提高你最昂貴查詢的多少性能？它是持久正面影響么？如果這些答案是“否”或“不知道”，那么定期索引維護可能不是個長遠的答案。最后，值得注意的是對小索引維護是不值得的。通常引用的閾值是近1000頁。在微軟，當Paul Randal管理存儲引擎開發團隊時，建議這些值作為參考，在在線幫助里記錄了。注意，盡管這只是個建議并不對所有環境合適，如Paul在他的博客文章里談到的：“在線幫助的索引碎片閾值來自哪里？”

調查重日志寫入事務

sys.dm_tran_database_transactions的DMV提供在事務日志上事務活動影響的有用內部信息。在他們的書里，《使用SQL Server動態管理視圖進行性能調優》，得到他們的允許后，復制在這里，作者Louis Davidson和Tim Ford，演示了如何使用這個DMV和一些其他的，來調查可能造成事務日志過度增長的事務。

在代碼7.1里的例子重用來自第6篇的FullRecovery數據庫和PrimaryTable_Large表。在一個顯性事務里，它重建了聚集索引然后調查日志增長。

1 USE FullRecovery

2 GO

3 BEGIN TRANSACTION

4

5 ALTER INDEX ALL ON dbo.PrimaryTable_Large REBUILD

6

7 SELECT DTST.[session_id],

8 DES.[login_name] AS [Login Name],

9 DB_NAME (DTDT.database_id) AS [Database],

10 DTDT.[database_transaction_begin_time] AS [Begin Time],

11 DATEDIFF(ms, DTDT.[database_transaction_begin_time], GETDATE())

12 AS [Duration ms] ,

13 CASE DTAT.transaction_type

14 WHEN 1 THEN 'Read/write'

15 WHEN 2 THEN 'Read-only'

16 WHEN 3 THEN 'System'

17 WHEN 4 THEN 'Distributed'

18 END AS [Transaction Type],

19 CASE DTAT.transaction_state

20 WHEN 0 THEN 'Not fully initialized'

21 WHEN 1 THEN 'Initialized, not started'

22 WHEN 2 THEN 'Active'

23 WHEN 3 THEN 'Ended'

24 WHEN 4 THEN 'Commit initiated'

25 WHEN 5 THEN 'Prepared, awaiting resolution'

26 WHEN 6 THEN 'Committed'

27 WHEN 7 THEN 'Rolling back'

28 WHEN 8 THEN 'Rolled back'

29 END AS [Transaction State],

30 DTDT.[database_transaction_log_record_count] AS [Log Records],

31 DTDT.[database_transaction_log_bytes_used] AS [Log Bytes Used],

32 DTDT.[database_transaction_log_bytes_reserved] AS [Log Bytes RSVPd],

33 DEST.[text] AS [Last Transaction Text],

34 DEQP.[query_plan] AS [Last Query Plan]

35 FROM sys.dm_tran_database_transactions DTDT

36 INNER JOIN sys.dm_tran_session_transactions DTST

37 ON DTST.[transaction_id] = DTDT.[transaction_id]

38 INNER JOIN sys.[dm_tran_active_transactions] DTAT

39 ON DTST.[transaction_id] = DTAT.[transaction_id]

40 INNER JOIN sys.[dm_exec_sessions] DES

41 ON DES.[session_id] = DTST.[session_id]

42 INNER JOIN sys.dm_exec_connections DEC

43 ON DEC.[session_id] = DTST.[session_id]

44 LEFT JOIN sys.dm_exec_requests DER

45 ON DER.[session_id] = DTST.[session_id]

46 CROSS APPLY sys.dm_exec_sql_text (DEC.[most_recent_sql_handle]) AS DEST

47 OUTER APPLY sys.dm_exec_query_plan (DER.[plan_handle]) AS DEQP

48 WHERE DB_NAME(DTDT.database_id) = 'FullRecovery'

49 ORDER BY DTDT.[database_transaction_log_bytes_used] DESC;

50 -- ORDER BY [Duration ms] DESC;

51 COMMIT TRANSACTION

（代碼7.1：調查重日志寫入事務）

（插圖7.1：索引重建后日志活動結果）

順便提下，如果我們用ALTER INDEX...REORGANIZE來運行這個例子，那么在Log Bytes Used列的值會從近159M降為近0.5M。

缺少日志空間重用

如果你懷疑缺少日志空間重用造成了日志增長，你的第一個任務是找出什么阻止了重用。開始通過查詢如代碼7.2所示的sys.databases，看下對于提到的數據庫log_reuse_wait_desc的列值錯誤信息是什么。

1 SELECT name ,

2 recovery_model_desc ,

3 log_reuse_wait_desc

4 FROM sys.databases

5 WHERE name = 'FullRecovery'

（代碼7.2：檢查下log_reuse_wait_desc的列值）

log_reuse_wait_desc的列值會展示為什么當前空間不被重用的原因。如果你已經執行剛才的例子（代碼7.1），那么很可能FullRecovery數據庫在這列會顯示LOG_BACKUP值（下面會詳談）。

阻止日志重用不止一個。sys.databases視圖只顯示其中一個原因。因此它是解決問題的一個可能方法，再次查詢sys.database會看到log_reuse_wait不同的原因。

在在線幫助里列出了log_reuse_wait_desc所有可能值，但在這里我們只談最常見的原因，解釋如何安全確保那個空間可以被重用。

沒有日志備份的完整恢復模式

如果從sys.databases查詢，log_reuse_wait_desc的返回值是LOG_BACKUP，那么你很可能遭受完整或大事務日志的最常見原因，即在完整恢復模式里的數據庫（或次之，大容量日志恢復模式），沒有進行事務日志備份。

在SQL Server的很多版本里，model數據庫默認是完整恢復模式。因為model數據庫是創建所有新SQL Server用戶數據庫的模板，新的數據庫繼承自model的配置。

對于大多數生產數據庫，使用完整恢復模式是推薦的做法，因為它允許數據庫的時間點恢復，最小化災難事件的數據丟失。但是，接下來的常見錯誤是調整備份策略是只有完整備份（或者有差異備份）而沒有定期的事務日志備份。這個策略有2個大問題：

進行完整數據庫備份只保護數據文件內容，沒有日志文件內容。完整保護已改變數據的唯一方法是自完整或差異備份后，需要時間點的恢復，是進行日志備份。

完整數據庫備份不會截斷事務日志。只有日志備份會造成日志文件截斷。沒有的話，日志文件里的空間是從不標記重用的，日志文件會不停的增長。

為了進行時間點的恢復并控制日志大小，我們必須用數據庫完整或完整和差異備份連同事務日志備份。對于我們的FullRecovery數據庫，我們可以進行日志備份，如代碼7.3所示，然后再次查詢sys.databases。

1 USE master

2 GO

3 BACKUP LOG FullRecovery

4 TO DISK = 'D:\SQLBackups\FullRecovery_log.trn'

5 WITH INIT

6 GO

7

8 SELECT name ,

9 recovery_model_desc ,

10 log_reuse_wait_desc

11 FROM sys.databases

12 WHERE name = 'FullRecovery'

（代碼7.3：解決日志備份問題）

如果缺少日志備份是日志增長問題的原因，首先要做的是驗證問題數據庫是否真的需要運行在完整恢復模式。如果必須要能恢復數據庫到任意時間點或到災難事件前的一個時間點，則是必須的，或者必須要用完整恢復模式的另一個原因（例如數據庫鏡像）。如果在SLA里目標恢復點（ Recovery Point Objective (RPO) ）為最大15分鐘的數據丟失，那么很可能你不能只進行完整數據庫備份和差異數據庫備份，必須要進行日志備份。

但是，如果因為不需要而沒有進行日志備份，那么數據庫不應該運行在完整恢復模式；我們可以切換數據庫到簡單恢復模式，那事務日志的不活動部分會自動標記為可重用，在檢查點的時候。

如果數據庫需要運行在完整恢復模式，那么開始日志備份，或調查下備份需要的頻率。事務日志的備份頻率取決于很多因素，例如數據修改的頻率，還有在災 難中，SLA上可接受的數據丟失程度。另外，你應該采取措施保證日志增長是可控的，在將來是可預見的，在這篇文章里的妥當的日志管理部分會介紹。

活動事務

如果log_reuse_wait_desc的返回值是ACTIVE_TRANSACTION，那么你受到來自SQL Server里完整或大的事務日志的第二個常見原因：長時間運行或未提交的事務。重新執行下來自代碼7.1的事務，但不提交，在重新執行下代碼7.3，你 會看到這個值返回（不要忘記回去提交這個事務）。

如在第2篇日志截斷和空間重用部分介紹的，事務日志里的VLF只有在不包含活動日志部分時才會被截斷。如果數據庫試用完整或大容量日志恢復模式，只 有日志備份操作才可以進行截斷。數據庫里長時間運行的事務延遲包含事務開始后生成的日志記錄的VLF的截斷，包括其它并發事務對數據庫里的數據修改產生的 日志記錄，甚至當這些改變還沒提交時。另外，長時間運行的事務的空間需求量會通過對“補償日志記錄”保留的空間增加，如果在系統里事務回滾的話，這些日志 記錄就會產生。這些保留是需要的，保證在回滾期間，這些事務可以成功恢復而不會用完日志空間。

另一個常見對log_reuse_wait_desc值的活動事務值是“孤立”的顯式事務，它莫名其妙的從不提交。允許用戶在事務里輸入的應用程序就特別容易是這類問題。

長時間運行的事務

造成長時間運行的事務的最常見操作，也是在數據庫里生成大量日志記錄，是從數據庫里歸檔或清除數據。數據保持往往是數據庫設計里事后的想法，經常是數據庫已經活躍一段時間后才考慮，是在服務器接近可用存儲的容量限制。

通常，當需要歸檔時，第一個反應是從數據庫里使用簡單的DELETE語句刪除不需要的數據，如代碼7.4所示。為了生成一些簡單的測試數據，這個腳本使用Jeff Moden的隨機數據生成器的簡化版本，簡單修改來生成日期到2012。

USE FullRecovery ;

GO

IF OBJECT_ID('dbo.LogTest', 'U') IS NOT NULL

DROP TABLE dbo.LogTest ;

SELECT TOP 500000

SomeDate = CAST(RAND(CHECKSUM(NEWID())) * 3653.0 + 37534.0 AS DATETIME)

INTO dbo.LogTest

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2 ;

-- delete all but the last 60 days of data

DELETE dbo.LogTest

WHERE SomeDate < GETDATE() - 60

（代碼7.4：大容量數據刪除）

取決于要刪除的在日期范圍內存在的行數，這會變成引起日志增長問題的長時間運行的事務，即使數據庫運行在簡單恢復模式。外鍵串聯約束的出現或審計觸 發器會惡化問題。如果其它表引用目標表，通過外鍵約束來級聯刪除，那么SQL Server頁通過級聯約束來記錄刪除的行的細節。如果表上有DELETE觸發器，在觸發器執行期間，SQL Server也會記錄進行的操作。

為了最小化在事務日志上的影響，數據清理應該簡化為更短，獨立的事務。有很多方法中斷長時間運行的事務為小的批處理。如果表存在級別約束或DELETE觸發器，我們可以在循環內進行刪除操作，在一個時間刪除一天的數據，如果代碼7.5所示。注意，在這個簡單的例子里，在我們的表里沒有足夠的行來驗證這個技術的使用，簡單的DELETE；清理幾百萬行數據更合適。也注意批量刪除的主要關心的是并不是速度（代碼7.5會比代碼7.4運行更慢）。最要關心的是避免日志過度增長和鎖升級。

DECLARE @StopDate DATETIME ,

@PurgeDate DATETIME

SELECT @PurgeDate = DATEADD(DAY, DATEDIFF(DAY, 0, MIN(SomeDate)), 0) ,

@StopDate = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 60, 0)

FROM dbo.LogTest

WHILE @PurgeDate < @StopDate

BEGIN

DELETE dbo.LogTest

WHERE SomeDate < @PurgeDate

SELECT @PurgeDate = DATEADD(DAY, 1, @PurgeDate)

END

（代碼7.5：將數據清除拆散為小的事務）

使用這個方法清除數據，每個刪除事務的持續時間是從表里刪除一條記錄的時間，加上任何觸發器或級聯約束進行它們操作的時間。如果數據庫使用簡單恢復模式，下個檢查點會截斷這些清除產生的日志記錄，只要在VLF里沒有相關數據清理的活動日志存在。

當在處理過程中級聯約束或審計觸發器不是要考慮的，我們可以使用不同的方法來清理表上的數據，同時最小化事務。不是進行一點的DELETE操作，它會影響多少的數據，取決于指定日期里存在的行數，在DELETE語句里使用TOP運算符會限制每個循環操作影響的行數。使用@@ROWCOUNT來捕獲DELETE操作影響的行數，運算符會在小的批處理語句里清除數據，直到@@ROWCOUNT的值小于DELETE語句里TOP子句里指定的行數，如代碼7.6所示。

這個方法只有在沒有觸發器和級別約束時使用有效，不然的話@@ROWCOUNT的結果不是實際表刪除的行數，而是觸發器執行或通過強制級聯約束影響的行數。

1 DECLARE @Criteria DATETIME ,

2 @RowCount INT

3 SELECT @Criteria = GETDATE() - 60 ,

4 @RowCount = 10000

5 WHILE @RowCount = 10000

6 BEGIN

7 DELETE TOP ( 10000 )

8 FROM dbo.LogTest

9 WHERE SomeDate < @Criteria

10 SELECT @RowCount = @@ROWCOUNT

11 END

（代碼7.6：對于數據清理在DELETE語句里使用TOP運算符）

這些方法在SQL Server 2000,2005,2008的任何版本都可以使用，在數據清理期間最小化事務。

但是，如果你的數據庫是SQL Server 2005或2008企業版，且經常清理數據，那么清理數據的更好方法是表分區，在列上篩選要刪除的數據。這會更小影響事務日志，因為分區包含的數據會從表轉出并清理，對SQL Server只是記錄區重新分配的操作。

管理存檔

這已經是這個系列文章討論范圍之外了，自動歸檔方案。但是，一個可能的歸檔過程涉及分區，表之間的架構復制，允許一個表的一個分區可以轉出到另一個。在主要的OLTP表最小化數據的活動部分，但只減少修改的元數據的歸檔過程。Kimberley Tripp已經寫了一份具體的白皮書，叫做SQL Server 2005里的表和索引分區，它談了劃窗技術（sliding window technique）。

未提交事務

默認情況下，SQL Server會在隱性事務里包裹任何數據修改語句來保證，在災難事件里，SQL Server可以回滾在故障點已經做出的修改，返回數據到一致的狀態。如果修改成功，隱性事務會提交到數據庫。和自動發生的隱性事務相比，我們創建顯性事務，在代碼包裹多個修改在一個事務里，來保證所有的修改通過ROLLBACK命令可以撤銷，或者通過COMMIT命令提交讓它持久。

當恰當使用時，顯性事務可以保證多個表之間的數據修改作為一個單位成功完成，或者全部都不修改。當使用不當時，不管怎樣，在數據庫里孤立的事務還是活躍的，阻止事務日志的截斷，這會導致事務日志增長或填滿。在SQL Server里有很多孤立事務的原因，這超出了這篇文章詳細介紹的范圍。但是，一些常見的原因有：

長時間運行的事務造成應用程序超時

在T-SQL或應用程序代碼里錯誤的錯誤處理

觸發器執行期間失敗

鏈接服務器失效導致孤立的分布式事務

和BEGIN TRANSACTION COMMAND沒有對應的COMMIT/ROLLBACK語句

一旦一個事務開始，它會保持活動直到創建的連接，事務觸發COMMIT或ROLLBACK語句，或者連接從SQL Server中斷（當使用綁定的鏈接，會允許會話共享鎖，這是個異常）。

現在的應用程序通常會使用連接池，在池里保持與SQL Server的連接讓程序重用，即使當程序代碼在連接上調用Close()方法。當對孤立事務進行故障排除是理解最后一點非常重要，因為即使連接在加入或返回到應用程序連接池前被重置，數據庫里打開的事務還是繼續存在的，如果它們沒有正常結束的話。

識別活動事務

事務相關的DMV提供大量的額外信息，不管當前事務的狀態和進行的操作。但是，一些DBA還是使用DBCC OPENTRAN作為識別是否為孤立事務（或只是長時間運行的）為造成日志增長的根源的最快方法。

在DBCC OPENTRAN(DatabaseName)格式里會接受數據庫名稱作為輸入參數，數據庫名稱是用作檢查打開事務的數據庫名。如果數據庫里有活動事務存在，命令會輸出類似如下的信息。

1 DBCC OPENTRAN (FullRecovery)

Transaction information for database 'FullRecovery'.

Oldest active transaction:

SPID (server process ID): 56

UID (user ID) : -1

Name : user_transaction

LSN : (897:15322:1)

Start time : Sep 18 2012 1:01:29:390PM

SID : 0x010500000000000515000000fd43461e19525f12828ba628ee0a0000

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

（代碼7.7：來自DBCC OPENTRAN的輸出信息范例）

DBCC OPENTRAN只輸出最早的活動事務，但主要表示事務是否為活動的疑問是開始時間。一般來說，未提交的事務是打開很長時間才會是造成事務日志增長的原因。

另一個重要的信息是SPID（server process ID；在DMV里這用session_id代替），這用來標識創建打開事務的會話。我們可以通過SPID判斷事務是真的孤立還是只是長時間運行的，通過查詢sysprocesses視圖（在SQL Server 2000里）或者SQL Server 2005及后續版本里的sys.dm_exec_sessions和sys.dm_exec_connections的動態視圖，如代碼7.8所示。注意sysprocesses視圖在SQL Server 2005及后續版本還是可用的，保持向后的兼容性。在運行代碼7.8時，在每個查詢里，直接用你看到的會話值替換session_id值（我們注釋了幾列，只是為了簡化輸出的可讀性）。

1 USE master

2 GO

3 SELECT spid ,

4 status ,

5 -- hostname ,

6 -- program_name ,

7 -- loginame ,

8 login_time ,

9 last_batch ,

10 ( SELECT text

11 FROM ::

12 fn_get_sql(sql_handle)

13 ) AS [sql_text]

14 FROM sysprocesses

15 WHERE spid = 53

16

17 USE FullRecovery

18 GO

19 SELECT s.session_id ,

20 s.status ,

21 -- s.host_name ,

22 -- s.program_name ,

23 -- s.login_name ,

24 s.login_time ,

25 s.last_request_start_time ,

26 s.last_request_end_time ,

27 t.text

28 FROM sys.dm_exec_sessions s

29 JOIN sys.dm_exec_connections c ON s.session_id = c.session_id

30 CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t

31 WHERE s.session_id = 53

（代碼7.8：使用DMV來識別孤立的還是長時間運行的事務）

如果回話是runnable，running或suspended狀態，那么可能問題的根源是長時間運行，而不是孤立的事務。但是，只有進一步的調查才能確認。很有可能剛才的事務失敗且連接重置，使用連接池，當前運行的語句不是打開事務所關聯的。

在SQL Server 2005和后續版本，我們可以使用sys.dm_tran_session_transactions和sys.dm_tran_database_transactions對打開的事務收集信息，包括事務開始事件，打開事務使用的日志數，以及日志空間使用字節數，如我們剛才代碼7.1所見。代碼7.9展示了一個簡單的版本，帶有范例輸出。

（代碼7.9：收集打開事務的信息）

除非應用程序設計來檢查，處理孤立的事務，清除事務的唯一方法是KILL會話，它會造成事務回滾，和連接中斷一樣，在下一次日志備份期間，允許日志里的空間是可以被重用的。但是，回滾的執行后果必須要理解的。

其他引起日志增長的可能原因

除了剛才提到的原因之外，還有其他一些問題阻止日志里空間重用，導致日志過度增長。這里我會談其中的一些，這個問題的更多信息，可以看下Gail Shaw的文章，為什么我的事務日志滿了？

REPLICATION

在事務復制期間，日志讀取代理的任務是讀取事務日志， 查找關聯修改的日志記錄，復制到訂閱者（例如，“待定的復制”）。一旦修改被復制，會標記日志為“已復制”。緩慢或延遲的日志讀取活動會導致記錄剩為“待 定的復制”很長時間，在此期間它們還是活動日志的一部分，因此母VLF不能被截斷。對于通過變更數據捕獲（ Change Data Capture (CDC)）功能需要的日志記錄也有類似的問題存在。

不管任何情況，sys.databases的 log_reuse_wait_desc列會顯示REPLICATION作為問題根源。在事務磁盤陣列的輸出性能里，這個問題本身也暴露了瓶頸。尤其是， 在并發寫加載下的延遲讀取操作。寫入日志文件會持續發生，但用日志讀取代理相關的和日志備份文件讀取的讀操作也要持續的。同一時間有持續的讀和寫發生，取 決于系統中的日志活躍級別和活動日志部分的大小，會導致磁頭隨機的I/O活動，因為磁頭需要改變位置來讀取活動日志的頭，然后活動日志的尾。我們可以使用性能監視器（PerfMon）里磁盤計數器 Physical Disk\Disk Reads/sec 和 Physical Disk\Disk Writes/sec來故障排除這類問題，看下SQL Server的故障排除的免費電子書的第2章來進一步了解這個問題的細節：https://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

這些復制等待問題的故障排除的第一步是識別日志讀取器，SQL 代理作業是否正常運行。如果不是的話，嘗試啟動它們。如果啟動失敗，你要找出為什么。

如果作業是運行的，但是復制一直等待，事務日志快速增長，你需要找到一些方法讓相關的日志標記為“已復制”，這樣的話它們的母VLF可以被重用。遺憾的是，沒有完美的解決方案來避免復制或在CDC環境里的副作用，但你可以嘗試下面方法中的一種。

在事務日志復制的情況下，使用sp_repldone命令來標記在日志讀取器上當前正等待的所有日志記錄為已復制，但還是需要重新初始化訂閱者，CDC的話，這個命令不會解決事務日志增長的問題。

停用CDC或復制，進行數據的人為同步。停用CDC或復制后，事務日志中的待定復制的日志記錄不會是待定，在完整或大容量日志恢復模式里的下次日志備份，或簡單恢復模式里的檢查點操作，會清除掉。但是，換來的代價是對于CDC環境需要數據的人為的同步，對于復制需要人為初始化訂閱者，如果這個功能加回到數據庫的話。

記住直接切換到簡單恢復模式，希望能截斷日志，是不行的，因為復制和CDC2個均不支持簡單恢復模式，還是繼續需要日志讀取器直到日志讀取器的SQL代理處理完成處理。

快照復制架構改變問題

在SQL Server 2005里使用快照復制有一個已知的問題，當架構修改時，它會導致應該標記為復制的架構修改沒被標記。這個問題可以看下這個文章的解決方法：http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/01/size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to-snapshot-replication.aspx

ACTIVE_BACKUP_OR_RESTORE

當log_reuse_wait_desc column列顯示為ACTIVE_BACKUP_OR_RESTORE作為當前等待描述，長時間運行的數據庫完整或差異備份是最有可能導致日志重用問題。在數據庫完整或差異備份期間，備份過程會延遲日志截斷，這樣的話事務日志的活動部分會被包含為完整備份的一部分。在備份操作沒完成期間，允許修改到數據庫的頁，當備份用WITH RECOVERY恢復時，可以讓數據庫恢復到一致的狀態。如果這樣的等待造成持續的問題，你會需要調查下優化備份過程的方式，例如提高備份性能（提供備份壓縮）或者提高硬盤I/O系統的性能。

DATABASE_MIRRORING

當log_reuse_wait_desc column列顯示為DATABASE_MIRRORING，作為當前等待描述，異步數據庫鏡像操作可能導致日志重用問題。

在異步鏡像里，主上的事務只有一旦提交，相關的日志記錄才會傳輸到鏡像數據庫。對于異步數據庫鏡像，主的日志不能截斷直到日志記錄已傳輸。當鏡像問題發生時，主上大量的日志記錄會保持為活動日志的一部分，阻止日志空間重用，直到復制到鏡像完成。

對于異步數據庫鏡像，如果鏡像不可用我們會看到DATABASE_MIRRORING，歸因于斷開或非常慢的連接，或鏡像會話的掛起。對于異步數據庫鏡像，在正常操作和連接問題期間，我們會看到這個值。

在這個情況下，首先我會檢查下受影響數據庫的鏡像會話狀態。如果它們沒有正確同步，那么你需要在主和鏡像之間故障排除失敗連接的原因。數據庫鏡像一個最常見的原因，當證書用來保證安全終端時，是證書過期，需要重新頒發證書。進一步討論鏡像連接問題處理已經不是這個文章的討論范圍，除非數據庫已經正常同步，那么日志記錄會發送到鏡像，在主上事務日志的活動部分會繼續增長，不能截斷直到中斷鏡像配置。

如果在主上的日志率大大超過可以傳送到鏡像的日志率，那么主上的日志會快速增長。如果鏡像服務器用來做報表，通過創建快照，對鏡像驗證磁盤I/O配置沒有飽和，通過剛才提高的性能監視器里硬盤計數器。如果這是問題所在，停止鏡像服務器的服務器可以臨時解決問題。如果問題是嚴格的大量事務，數據庫沒有運行在SQL Server 2008或更高，那么升級可以解決問題，因為可以使用SQL Server 2008或更高版本的日志流壓縮。

最好的方法是判斷鏡像問題的原因并解決它。例如，調優生成大量日志記錄的操作，例如大容量加載數據，或者重組索引，在操作期間可以減少對系統的影響。

處理事務日志滿錯誤

最壞的情況，事務日志管理不當或突發、快速的日志增長會造成事務日志增長，最后吞食完硬盤上所有可用空間。到這個時候就不能增長了，你會遇到9002錯誤，事務日志滿錯誤，數據庫會變成只讀。

盡管這個問題很緊迫，冷靜面對很重要，避免這類接下來會提到的”無意識“的解決方法，處理不當或做不該做。顯然當前的問題是讓SQL Server可以繼續寫日志，通過生成更多可用空間。如果起因是缺少日志備份，第一個要做的是重新運行代碼7.1；如果log_reuse_wait_desc列返回值是 Log Backup，那么和可能這是問題原因。一個在MSDB數據庫里對backupset表的查詢，如代碼7.10所示，會確認是否要在數據庫上進行一次日志備份，還有上一次日志備份的時間。

1 USE msdb ;

2 SELECT backup_set_id ,

3 backup_start_date ,

4 backup_finish_date ,

5 backup_size ,

6 recovery_model ,

7 [type]

8 FROM dbo.backupset

9 WHERE database_name = 'DatabaseName'

（代碼7.10：哪個備份已做，什么時候做的）

在type列，D代表數據庫備份，L代表日志備份，I代表差異備份。如果沒有日志備份，或者它們并不頻繁，那么你最好的做法是進行一次日志備份（這里假定數據庫運行在完整或大容量日志恢復模式）。希望，這個能釋放日志里的實在空間，然后你可以進行合適的日志備份計劃和日志增長管理策略。

如果因為某些原因不能進行日志備份，例如磁盤空間不足，或者進行日志備份的時間超過可接受的問題解決時間，那么，取決于對問題數據庫的災難恢復策略，或許可以通過臨時切換到簡單恢復模式來強制日志截斷，這樣在檢查點的時候日志中不活動的VLF會被截斷。然后你可以切換回完整數據庫恢復模式，進行新的完整數據庫備份（或差異備份，這里假定先前已經有一次完整備份）來重新開始用于時間點恢復的日志鏈。當然，你還是充分調查問題，來保證空間不會再次直接吞食完。還有記住這點，剛才討論過的，如果阻止空間重用的問題不是日志備份，那么這個技術就無效了，因為這些記錄會保留在活動日志里，阻止截斷。

如果缺少日志備份不是問題，或者進行完日志備份不能解決問題，那么調查原因可能會花更多的時間。最快和最簡單的方法是在日志硬盤上增加更多的空間。這表示要清理掉其他文件，或者增加當前日志硬盤的容量，或者在不同的硬盤列里增加額外日志文件，但這會占用你一點喘息的空間，你需要讓數據庫擺脫只讀模式，然后進行一次日志備份。

如果日志備份釋放空間失敗，你要找出什么阻止了日志里的空間重用。調查下sys.databases（代碼7.1）來找出什么阻止了日志空間重用，采取合適的行動，如剛才缺少日志空間重用部分介紹的。

如果這個啥都沒透露，你需要進一步調查找出什么操作造成過度日志導致日志增長，如事務日志過度增長部分介紹的。

最后，解決了任何空間重用問題，很可能我們的日志文件會在磁盤上占用很大的空間。作為一次性的測量，例如假定我們采取措施保證日后日志增長有妥善的管理（下一部分就會談到），是可以使用DBCC SHRINKFILE來回收臃腫事務日志文件使用的空間。在第8篇我們會提供如何做的例子。

我們要么指定收縮日志的文件target_size，要么指定0位目標大小，讓日志收縮的盡可能小，然后立即使用ALTER DATABASE來調整到合適的大小。后者是推薦的方法，它會最小化日志文件的碎片。碎片問題是你應該從不定期進行的DBCC SHRINKFILE任務的主要原因，因為它只用來控制日志大小；我們會在下個部分詳細討論這個。

處理不當和不該做的事

遺憾的是，在網絡上搜索”事務日志滿“會返回大量論壇的帖子，博客文章，甚至很多復制于SQL Server網站的文章，那些建議矯正的方法，坦白說，很危險。我們在這里會談其中一些流行的建議。

分離數據庫，刪除日志文件

這個方法，你清理了所有用戶的數據庫，分離數據庫（或者關閉它），刪除日志文件（或重命名），然后重新附加數據庫，會引起新的日志文件創建，它的大小由model數據庫決定。這可以說是處理完整事務日志的最可怕的方式。它會造成數據庫啟動失敗，數據庫為RECOVERY_PENDING狀態。

取決于數據庫在日志刪除時是否正常關閉，在數據庫正常部分的恢復階段，數據庫可能不能進行撤銷和重做操作，因為事務日志已經丟失，不能返回數據庫為一致的狀態。當日志文件丟失時，數據庫需要事務日志來進行故障恢復，數據庫不能正常啟動，只能從最近的可用備份里恢復數據庫，這就會導致數據丟失。

創建，分離，附加，修復可疑數據庫

在特定情況下，可以黑入現存的數據庫的配置，允許事務日志重建，但這會破壞數據庫里現有數據庫的完整性。這類操作是，最好是最后實在絕對沒法恢復數據庫數據了，這是我們這個系列文章不推薦的做法。至于如何嘗試黑入數據庫來看已刪除的事務日志，可以看下Paul Randal的文章：創建，分離，附加，修復可疑數據庫。

強制日志文件截斷

在SQL Server 2000 和2005，BACK LOG WITH TRUNCATE_ONLY是SQL Server支持的強制截斷事務日志的方法，在數據庫運行在完整或大容量日志模式。使用這個命令實際不會做日志內容備份副本；在截斷VLF里的記錄會忽略。因此，不像正常日志備份，你在破壞你的LSN鏈，你只能恢復數據庫到先前任何日志備份里的時間點。還有，即使數據庫設置為完整恢復模式，實際上，從那個點開始，會運行在自動截斷模式，在檢查點會繼續截斷不活動的VLF。為了讓數據庫運行在完整恢復模式，重新開始LSN鏈，你需要進行一次完整（或差異）備份。

沒有意識到對災難恢復的影響的人們才會經常使用這個命令，在SQL Server 2005里已經廢棄，從SQL Server 2008開始已經移除這個命令了。遺憾的是，這個技術更陰險的版本，還是繼續被支持，取而代之這個命令，那就是BACKUP LOG TO DISK='NUL'，NUL是忽略任何數據寫入的“虛擬文件”。這個技術的真正扭曲是，不像BACKUP LOG WITH TRUNCATE_ONLY，SQL Server不管日志記錄，直接忽略。就SQL Server而言，進行日志備份后，日志記錄會在備份文件里安全存儲，這樣的話，LSN鏈是完整的，在活動日志里的不活動VLF可以安全截斷。任何接下來，慣例的日志備份會成功，但從故障恢復的角度來說是無用的，因為日志備份文件丟失的話，數據庫只能恢復到上次標準日志備份的時間點，在BACKUP LOG TO DISK='NUL'命令發出前。

不要使用這里的任何技術。強制日志截斷的正確方法是臨時切換數據庫導簡單恢復模式，如前所述。

計劃事務日志收縮

如在處理事務日志滿錯誤部分討論的，事務日志在很少情況下是因為管理不當造成的，日志增長正被活動管理，使用DBCC SHRINKFILE來回收事務日志占用的空間是個可以接受的操作。

但我們絕不能把日志收縮作為日常，計劃維護操作的一部分。原因是我們每次收縮日志，它會為接下來的事務立即再次增長來存儲日志記錄。如在日志大小和增長部分討論的，事務日志不能利用即時文件初始化，因此所有日志增長引發SQL Server需要分配的存儲空間填零操作。另外，如果我們依賴事務日志自動增長（下部分會談到），在日志文件了會聚集更多的VLF，這個日志碎片會影響任何需要讀取這個日志文件的進程性能，如果碎片實在太多，也會影響到數據修改性能。

對于事務日志文件的最佳做法是預先設置好它的合適大小，這樣的話正常情況下就不會增長。然后，監視它的使用率來決定是否需要人為增長，允許你決定合適的增長大小且決定要添加到日志文件里的VLF的大小和個數。在第8篇我們會具體討論。

妥當的日志管理

沒有任何意想不到的操作或問題而導致不正常的日志增長（復制問題，未提交的事務等等），如果事務日志關聯的數據庫運行在完整恢復模式，還一直增長，其實只有2個原因：

日志文件大小太小，支持不了當前數據庫所發生的數據修改。

日志備份的頻率不夠，滿足不了日志文件里快速空間重用。

最好的做法，如果你不能通過減少它們之間的時間來增加日志備份的頻率，當在加載的時，可以人為增加日志文件大小而不是讓它自動增長，然后恢復原來大小。有大的我們人為增長的事務日志文件，但有最小化數量的VLF并不是個壞事，即使大部分時間日志文件有空余空間。我們會在第8篇詳細討論這個。

小結

對于SQL Server數據庫的操作，事務日志非常重要，還有在災難事件里能最小化數據丟失風險。在日志瘋狂增長的情況里，甚至滿了，DBA需要快速診斷并解決問題，同時要保持冷靜也非常重要，避免不深思熟慮的反應，例如強制日志截斷，還有計劃定期的日志收縮，這只會弊大于利。

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com