• 發行項

SQL Server

瞭解記錄] 及 [SQL Server 中修復

Paul S. Randal

 

簡介：

  • 記錄及復原在 SQL Server 的運作方式
  • 交易記錄檔的運作方式，而且您所需要知道管理
  • 復原模型及記錄其效果

內容

什麼記錄？
修復是什麼？
將交易記錄檔
復原模型
向上換行

某些 SQL Server 的最誤解部分是它的記錄及修復機制。事實上交易記錄檔存在並可能會造成問題如果沒有正確地管理似乎 confound 許多 「 involuntary DBA 」。 為什麼它可能會在交易記錄檔成長未繫結？為什麼不會它有時要花這麼久的上線之後系統損毀的資料庫？為什麼無法記錄被關閉完全？為什麼無法我復原我的資料庫正確？交易記錄檔 」 和 「 為什麼有是什麼，只是？

這些是重複看到 SQL Server 論壇] 和 [新聞群組中，所以本文中我想要提供 [記錄] 和 [修復系統的概觀，並將說明為什麼它是這類的整數部分 SQL Server 的儲存引擎的所有問題。我會說明的架構交易記錄檔，以及如何三個可供資料庫的復原模型，可以變更交易記錄檔和記錄處理程序本身的行為。的方式以及我也將提供一些連結涵蓋交易記錄檔的資源管理的最佳作法。

什麼記錄？

記錄] 和 [復原不是 SQL Server 特有的概念 — 所有的商業印刷的關聯式資料庫管理系統 (RDBMSs) 必須，支援各種交易的 ACID 屬性。ACID 代表不可部分完成性、 一致性、 隔離及耐久性的基本系統屬性的一個交易處理 (例如的 RDBMS)。您可以閱讀更多的資訊，MSDN Library 的 ACID 屬性] 區段.

視訊

Paul Randal 示範管理 「 完整 」 復原模式，正確您交易記錄檔的重要性，並他說明這樣做，在 SQL Server 的技術。

RDBMS 中的作業的記錄 (或記錄) 層級實體和邏輯方面的發生資料庫的存放結構的狀況。儲存結構之每個變更都有自己記錄檔資料錄，描述結構變更 」 和 「 變更為何。這是完成方式可以重新執行或回復，變更，必要時。會儲存在特殊的檔案，稱為交易記錄檔中，記錄檔資料錄，我會描述此詳細稍後，但是現在您可以視為的循序存取檔案。

一組的一或多個這類變更可以 (以及事實，永遠都是) 在交易中一起 — 這會提供基本單位對資料庫，使用者，應用程式開發人員，一樣方面進行變更 (不可部分完成性)，並將 DBA 的考量。交易成功 (認可) 或失敗 / 是可取消 (復原)。在第一種情況下的作業，表單的交易被保證會反映在資料庫。在第二種情況下作業會保證不會反映在資料庫。

在 SQL Server 的交易是明確或隱含)。明確交易是，使用者或應用程式發出 BEGIN TRANSACTION T-SQL 陳述式信號的一組相關的變更開始的工作階段。發出 COMMIT TRANSACTION 陳述式，信號變更的群組的成功完成時，則明確交易會成功。如果而發出 ROLLBACK TRANSACTION 陳述式，會還原所做的工作階段，因為發出 BEGIN TRANSACTION 陳述式的所有變更 (復原) 和交易已中止。例如資料庫用盡磁碟空間或在伺服器當機，我會稍後說明的外部事件，也是無法強制交易復原。

在隱含交易是，使用者或應用程式不明確發出 BEGIN TRANSACTION 陳述式之前發出 T-SQL 陳述式。不過的所有變更資料庫必須都為可交易的儲存引擎會自動啟動在幕後交易。T-SQL 陳述式完成時, 儲存引擎會自動認可交易，使用者的陳述式前後包裝它啟動。

您可能認為這是不需要因為單一的 T-SQL 陳述式不會產生大量的變更儲存在的資料庫的結構，但是考慮重建 ALTER INDEX 陳述式中有類似。雖然這個陳述式無法包含明確交易內，它無法產生的龐大資料庫的變更數目。因此必須有一些機制，確保如果項目不會出錯 (陳述式取消，執行個體) 的所有變更會正確還原。

例如，請考慮在隱含交易更新單一資料表資料列時。假設的整數資料行 c1 和字元的資料行 c2 具有簡單的堆積 (Heap) 資料表。資料表則含有 10，000 個的資料列]，而使用者送出更新查詢，如下所示：

UPDATE SimpleTable SET c1 = 10 WHERE c2 LIKE '%Paul%';

進行下列作業：

  • 從 SimpleTable 資料頁面會從磁碟讀入記憶體 (緩衝區集區) 讓您可以搜尋相符的資料列。 其實三個資料頁面會保留符合 WHERE 子句的述詞的五個資料列。
  • 儲存引擎會自動啟動的隱含的交易。
  • 五個資料列與三個資料頁面會鎖定允許更新。
  • 在記憶體中三個資料頁面上的五個資料記錄的變更。
  • 變更也會記錄在磁碟上交易記錄檔中記錄中。
  • 儲存引擎會自動認可隱含交易。

請注意，我未列出的步驟，其中三個更新的資料頁面寫回出到磁碟。 這是因為他們還不需要是，；，只要在記錄檔資料錄的說明會在交易記錄檔，磁碟上的變更，然後變更受到保護時。 如果頁面需要後續讀取或變更一次，然後頁面的最新的複本是已經就無法在磁碟 (但) 記憶體中。 資料頁面將會寫入出磁碟下一個檢查點作業發生時或記憶體緩衝區集區中使用，是需要的另一個頁面影像。

檢查點於兩個原因，以批次處理寫入 I / O 以改善效能並減少時間設定所需損毀修復。 效能，方面的如果資料頁面已強制出到磁碟的每次它已更新數目寫入 I / O 忙碌的系統上發生無法輕易地擊敗 I / O 子系統。 最好定期寫出不乾淨的網頁 (已變更後從磁碟讀取的網頁) 比以寫入頁面立即如有變更。 我將於稍後討論修復外觀的檢查點。

一個常見的誤解，相關的檢查點是它們只寫入頁的變更從已認可的交易。 這不為真，檢查點總是會寫出所有不乾淨的頁面，無論是否變更頁面的交易已認可或不。

記錄 Write-Ahead 將是您，其中描述變更的記錄會寫入磁碟本身的變更會寫入之前的機制。 它提供 ACID 屬性耐久性的部分。 只要描述變更的記錄位於磁碟，在的損毀的事件記錄檔記錄 (並因此本身的變更) 可以復原，而且將異動的效果不遺失。

修復是什麼？

尋找 SQL Server 的秘訣嗎？

在 SQL Server 上的提示，請造訪， TechNet Magazine 》 的 SQL Server 祕訣 頁面。

如需其他產品的更多秘訣，造訪， TechNet Magazine 秘訣索引.

為支援各種作業，在 SQL Server 中存在的記錄。 它確保損毀發生認可的交易將會正確地反映在資料庫損毀之後。 它可確保的未認可的交易將會正確復原並在當機之後不會反映在資料庫中。 它可確保時，可以取消的 「 執行中 」 的交易，並有其所有的作業復原。 它可以讓要採取以便可以還原資料庫交易記錄檔和使資料庫至特定的點的時間與交易一致性的重新顯示交易記錄檔備份的備份複本。 並支援依賴讀取交易記錄檔例如複寫、 資料庫對映功能和變更的資料擷取功能。

大多數的這些記錄使用牽涉到一個機制，稱為修復。 修復會是變更記錄檔記錄重新顯示，或還原資料庫中所述的程序。 重新顯示記錄檔資料錄稱為取消復原 (或向前) 的復原階段。 還原記錄檔資料錄稱為 [復原] (或回復) 的復原階段。 也就是說修復會讓確定交易和其所有構成的記錄檔記錄都是 Redone 或復原。

簡單格式的修復，單一的交易已取消，在這種情況下它是復原，並在資料庫上沒有網路的影響。 在最複雜的表單則是損毀修復，當 SQL Server 損毀 (針對任何的原因)，並使資料庫交易的一致性的點必須復原交易記錄檔。 這表示所有的交易已認可在損毀時必須要向前復原以確保其效果會保存在資料庫中。 並有無法認可在損毀時的所有 「 執行中 」 交易必須進行復原以確保其效果不會保存在資料庫中。

這是因為沒有 SQL Server 中的交易在當機之後繼續沒有設備。 因此，如果部分完成交易的效果無法復原執行，資料庫就會處於不一致的狀態 (可能是即使結構上毀損，根據交易已做的中間)。

復原如何沒有知道該怎麼辦？ 所有的復原處理程序而定，每個記錄檔資料錄戳記記錄序號 (LSN)。 記錄的序號會是唯一定義的記錄檔資料錄在交易記錄檔位置的不斷增加，三個部分號碼。 在交易的每個記錄檔資料錄儲存在交易記錄檔內的循序順序，並包含交易識別碼] 和 [交易的前一個記錄檔資料錄的 [LSN。 也就是說做為交易的一部分記錄的每個作業會有 「 連結 」 回到立即將它之前的作業。

單一異動的復原的簡單案例，修復機制，可以輕易地快速回到第一個作業遵循最近一次作業記錄作業的鏈結並復原作業發生在以相反順序的效果。 影響交易資料庫頁面會是仍在緩衝區集區或磁碟上。 不論是哪的種情況，頁面可用的影像被保證為交易的效果，會反映在頁面上，必須復原。

在損毀修復，期間，機制都是更複雜的。 資料庫頁面會不寫入磁碟時交易認可，表示有保證在磁碟上資料庫頁面的集合準確反映的交易記錄檔中所述的變更集 — 可能的認可或未認可的交易。 但是，沒有一個最後一段，我還沒提到的拼圖 — 所有的資料庫頁面會有其頁面標頭 (96 位元部分 8192 個位元組包含之網頁的相關頁面中的繼資料) 中的欄位包含在受影響的頁面的最後一個記錄檔資料錄的 LSN。 這可讓復原系統決定是要提供關於特定記錄檔記錄，它必須復原：

  • 從已認可的交易，其中的 [資料庫] 頁面有的 LSN 等於或大於 [LSN 記錄檔資料錄的資料記錄檔] 錄任何需要進行。 在磁碟上的頁面上，已經已保存記錄檔資料錄的效果。
  • 認可交易，其中的 [資料庫] 頁面有的 LSN 小於，LSN 記錄檔資料錄的記錄檔記錄，必須取消復原記錄檔資料錄以確保交易的效果會保存。
  • 從其中的 [資料庫] 頁面有的 LSN 等於或大於在記錄檔資料錄的起始 LSN 的未認可交易記錄檔資料錄，必須以確保不會保存交易效果會復原記錄檔資料錄。
  • 從 [資料庫] 頁面其中有的 LSN 小於，LSN 記錄檔資料錄的未認可交易的資料記錄檔] 錄任何需要進行。 記錄檔資料錄的效果不會保存在磁碟上的頁面上，而且因此不需要為復原。

損毀修復會透過交易記錄檔讀取，並確保所有經過認可之交易的所有效果都保存在 [在的資料庫，而且所有未認可交易的所有效果不會都都保存在資料庫中，[復原] 與取消復原階段，分別。 損毀修復完成資料庫會是交易一致性，並可供使用。

早我提在檢查點作業的用法之一來減少的損毀修復會的時間量。 藉由將所有 Dirty 頁定期清除到磁碟，減少的頁數，已變更因為已認可的交易中，但其影像不磁碟上。 這，依次，減少需要取消復原復原損毀復原期間所套用的頁面。

將交易記錄檔

如果交易記錄檔原封不動，損毀修復會是唯一的可能。 事實上，交易記錄檔是在最重要的一部分，資料庫的 — 是唯一的資料庫的所有變更會都保證損毀的事件描述的地方。

如果交易記錄檔遺失或損毀在當機之後，然後無法完成損毀修復，導致可疑的資料庫。 在這種情況下資料庫必須從備份還原，或復原使用例如緊急模式修復的較理想選項。 (這些程序已超出本文的範圍但將討論文件中深入稍後在年份中)。

交易記錄檔將是您，特殊的檔案，每個資料庫必須正常運作。 它會保留記錄由記錄所產生，用來一次] 期間復原 (或任何其他使用我所提到的記錄)，讀取它們。 以及記錄本身所佔用的空間，交易將也會保留空間如果要取消交易，並需要復原需要任何可能記錄交易記錄檔中。 這個帳戶行為可能會看到的位置，說，交易更新資料庫中的資料 50MB 可能實際上需要的交易記錄檔空間 100 MB]。

當在建立新的資料庫是時，交易記錄檔會是基本上是空的。 交易發生，記錄會寫入循序交易日誌，這表示沒有建立多個交易記錄檔的效能增益，常見的誤解。 交易記錄檔會輪流使用每個記錄檔] 檔。

交易記錄檔中，可以是散置並行交易記錄檔記錄。 請記住因此不需要所有的記錄檔記錄的交易會分組在記錄檔中，以單一的交易記錄檔記錄由其 LSNs，連結。 LSNs 可以幾乎被視為一個時間戳記。

交易記錄檔的實體架構如 [圖 1 ] 所示。 它會分成內部稱為虛擬記錄檔 (或 VLF) 的較小區塊。 這些都是直接更容易內部管理交易記錄檔的協助。 在 VLF 完整時自動記錄會繼續使用交易記錄檔的下一個的 VLF。 您可能會認為最後的交易記錄檔會執行的空間，] 但 [這是，交易記錄檔資料檔案讓不同。

fig01.gif

[圖 1) 的交易的實體架構記錄

交易記錄檔是真的循環檔案 — 只要在交易記錄檔的開頭的記錄具有已截斷 (或清除)。 然後當登入到達的交易記錄檔的結尾，它重新包裝 [開始]，並開始覆寫哪些之前已有。

那麼要如何取得截斷記錄以便可以重複使用它們所佔用的空間？ 如果下列所有都為 true，記錄檔資料錄已不再需要交易記錄檔中：

  • 它是在其中一部份的交易已認可。
  • 它變更資料庫頁面已所有寫入磁碟的檢查點。
  • 記錄檔資料錄不需要進行備份 (完整、 差異或記錄檔)。
  • 讀取記錄檔 (例如資料庫鏡像或複寫) 的任何功能並不需要記錄檔資料錄。

仍然需要在記錄檔記錄內將稱為 Active，而具有至少要有一個作用中的記錄檔記錄的 VLF 也稱為 Active]。 因此通常定期交易記錄檔會檢查以查看完整的 VLF 的所有記錄檔記錄是否作用中或不 ； 如果它們所有的非現用 [VLF 標示為截斷 (表示在 VLF 可以覆寫之後，交易記錄檔會包裝)。 截斷為 VLF 時, 它未覆寫或歸零以任何方式 — 它截斷為只標記，並接著可重複使用。

這個程序稱為記錄檔截斷命令) 無法與實際上壓縮交易記錄檔的大小搞混。 記錄檔截斷命令永遠不會變更的交易記錄檔的實體大小 — 只有的哪些部分交易記錄檔是現用或不。 [圖 2 ] 顯示 [圖 1 交易記錄檔截斷發生之後。

fig02.gif

[圖 2 ： 的交易記錄在記錄檔截斷命令之後

邏輯記錄檔設定現用的 VLF 讓 — 交易記錄檔，包含所有使用中的記錄記錄的部分。 損毀修復應該開始讀取記錄檔中記錄的現用部分的資料錄的資料庫本身知道 — 在記錄檔，MinLSN (這儲存在 [資料庫開機] 頁面)，最舊現用交易的開始。

損毀修復不知道要停止讀取記錄檔記錄以便繼續執行直到它到達 zeroed 的區段交易記錄檔 (如果交易記錄檔已不尚未包裝) 為止或記錄檔資料錄的同位檢查位元) 不符合從先前的記錄檔記錄的順序。

將邏輯的記錄 VLF 會被截斷，並成為現用的新，移到內實體的交易記錄檔，並最後應換行周圍，開始重新，所示 [圖 3] .

fig03.gif

[圖 3 ： 循環性質交易記錄檔

檢查是否記錄檔截斷命令可在下列情況下的位置：

  • 當檢查點或其他的復原模型中的簡單復原模式時發生完整備份有永遠不會採取。 (這表示資料庫在之後的切換 SIMPLE 的完整的資料庫備份之前會保留在 pseudo-SIMPLE 的復原模型)。
  • 記錄檔備份時完成。

請記得，記錄檔截斷可能可能因許多原因記錄檔資料錄必須保持作用中) 之。 當記錄檔截斷命令無法執行，無法截斷，VLF 並最後交易記錄檔成長 (或加入另一個交易記錄檔)。 過多的交易記錄檔成長，會造成效能的問題，透過一個稱為 VLF 分散的現象。 移除 VLF 片段有時候導致一個大幅改進記錄相關活動的效能。

如需此的更多資訊，請參閱 Kimberly Tripp 張貼的部落格 (英文) ＞ 步驟 8 獲得較佳的交易記錄檔的輸送量." Kimberly 討論有關交易記錄檔容量規劃，管理和效能也有所改善的最佳實務，也值得讀取的 ！

有兩個常見的問題，可以防止記錄檔截斷命令：

  • 長期執行的現用交易。 整個交易記錄檔之後第一個記錄檔記錄，從最舊的現用交易可以永遠不會被截斷之前的交易會認可或中止。
  • 切換 FULL 的復原模式，進行一個完整的備份，然後永遠不會採取任何記錄備份。 整個交易記錄檔都 Active 等待要備份的記錄檔備份。

如需有關如何判斷哪些讓記錄檔截斷命令的因素完整清單，請參閱 SQL Server 線上叢書 》 中的主題 (英文) ＞ 可以延遲的記錄檔截斷的因素." 我也在建立視訊的示範，顯示未受控制的交易記錄檔成長，以及如何移除 VLF 分散的效果-簽出這個視訊 screencast (和 SQL 主題先前 screencasts)，在 technetmagazine.com/videos.

如果交易記錄檔容量會增加，而且無法增加任何進一步，然後會報告錯誤 9002，您必須採取步驟來提供更多的空間例如成長的記錄檔，、 加入其他的記錄檔案，或移除任何 impediment，記錄檔被截斷。

在任何情況下應該您刪除交易記錄檔，嘗試重建使用未記載的命令或只是截斷它使用 NO_LOG 或 TRUNCATE_ONLY 選項的 BACKUP LOG (這在 SQL Server 2008 中已移除)。 這些選項將會導致交易不一致性 (和多個可能的損毀)，或移除可能的能夠正確復原資料庫。

如需有關如何疑難排解完整的交易記錄檔的詳細資訊，請 [出線上叢書 》 的主題] 」 疑難排解完整的交易記錄檔 (「 錯誤 9002 」)."

復原模型

您可以看到行為交易記錄檔的定部分在使用資料庫的復原模式。 有可用的的三個的復原模型，而且它們具有交易記錄檔行為、 如何記錄作業或兩者的影響。

FULL 的復原模式，表示每個作業的每個部分就會記錄，這稱為完整記錄。 一旦有 FULL 的復原模型中採取完整的資料庫備份，交易記錄檔將不會自動截斷之前採取一個記錄檔備份。 如果您不希望使用的記錄檔備份和能夠修復資料庫特定的時間，不使用 FULL 的復原模式。 不過，如果要使用資料庫鏡像則有任何的選擇為它只支援 FULL 的復原模式。

BULK_LOGGED 的復原模式有相同的交易記錄檔截斷語意與 FULL 的復原模型但可讓部分記錄，某些作業的呼叫最少式記錄。 範例包括的索引重建及某些大量載入作業 — FULL 的復原模型中，整個作業會記錄。

但在 BULK_LOGGED 復原模型中只能配置變更記錄，大幅減少產生的記錄檔資料錄，並依次，降低交易記錄檔成長的。 如需最少式記錄的作業的更多資訊，請參閱線上叢書 》 的區段 (英文) ＞ 可以無法最少式記錄的作業."

最後，簡易復原模式實際上會 Clock 與 BULK_LOGGED 修復的記錄行為相同但交易記錄檔截斷語意相當不同。 記錄檔備份沒有簡單的復原模型，表示 (只要沒有其他保留的記錄檔資料錄 Active)，可以被截斷記錄檔中可能檢查點時發生。 有專業人員和每個這些的哪個備份都是可能的 (或所需的)，並能夠的時間 (我將介紹這另一個文件中今年稍後) 復原到不同的點的復原模型的缺點。

向上換行

本文已經真正多 Academic 說明，如何在 SQL Server 的運作方式的一重要的部分。 我希望我已經設法清除您可能有任何 misconceptions。 如果這是您第一個簡介記錄及復原，如下我希望您從本文的重點：

  • 不要建立多個的記錄檔，因為它將不會導致增進效能。
  • 請注意，使用您的資料庫的復原模式以及它對交易記錄檔-是否可以自動解決，尤其是截斷或檢查點發生時不會。
  • 交易記錄檔成長的可能知道，回控制下的因素可能會導致它，及如何取得它。
  • 知道在何處尋找 [說明] 疑難排解完整的交易記錄檔時。

在 [我的部落格上，我會有更多的資訊，有關交易記錄檔及因素會影響它 — 請參閱我的部落格內容類別 」 交易記錄檔」 的詳細端內部網路]。 各種不同的線上叢書 》 主題有關交易記錄檔也會很好-開始 交易記錄檔管理.

為永遠，如果您有任何意見反應] 或 [問題，請卸除我在一行 Paul@SQLskills.com.

感謝到 Kimberly L。 提供此文件技術檢閱 Tripp。

Paul Randal S。 是管理的 Director SQLskills.com及一個 SQL Server MVP。 他處理 SQL Server 儲存引擎小組在 Microsoft 自 1999 年 2007。 Paul 撰寫 SQL Server 2005 的 DBCC CHECKDB / 修復並且負責核心的儲存引擎在 SQL Server 2008 開發期間。 Paul 是在嚴重損壞修復、 高可用性和維護資料庫的專家，也是一般在世界的會議主持人。 在他的部落格 SQLskills.com/blogs/Paul.