SQL Server事務 交易記錄

標籤：iso   刪除   version   控制   檔案中   16px   star   guide   磁碟故障   

事務 (SQL Server)

一、事務概念
    事務是一種機制、是一種操作序列，它包含了一組資料庫操作命令，這組命令要麼全部執行，要麼全部不執行。因此事務是一個不可分割的工作邏輯單元。在資料庫系統上執行並行作業時事務是作為最小的控制單元來使用的。這特別適用於多使用者同時操作的資料通訊系統。例如：訂票、銀行、保險公司以及證券交易系統等。
 
二、事務屬性
事務4大屬性：
1   原子性(Atomicity):事務是一個完整的操作。
2   一致性（Consistency）：當事務完成時，資料必須處於一致狀態。
3   隔離性(Isolation):對資料進行修改的所有並發事務是彼此隔離的。
4   持久性(Durability):事務完成後，它對於系統的影響是永久性的。
 
三、建立事務
T-SQL中管理事務的語句：
1 開始事務: begin transaction
2 提交事務：commit transaction
3 復原事務: rollback transaction
 
事務分類:
1 明確交易:用begin transaction明確指定事務的開始。
2 隱性事務：開啟隱性事務：set implicit_transactions on，當以隱性事務模式操作時，SQL Servler將在提交或復原事務後自動啟動新事務。無法描述事務的開始，只需要提交或復原事務。
3 自動認可事務：SQL Server的預設模式，它將每條單獨的T-SQL語句視為一個事務。如果成功執行，則自動認可，否則復原。

 

交易記錄 (SQL Server)Tue Oct 03 2017

每個 SQL Server 資料庫都有交易記錄，用於記錄所有事務以及每個事務所做的資料庫修改。

交易記錄是資料庫的一個關鍵組件。 如果系統出現故障，你將需要依靠該日誌將資料庫恢複到一致的狀態。

重要

永遠不要刪除或移動此日誌，除非你完全瞭解執行此操作的後果。

提示

檢查點會建立一些正常點，在資料庫恢複期間將從這些正常點開始應用交易記錄。 有關詳細資料，請參閱資料庫檢查點 (SQL Server)。

交易記錄支援的操作

交易記錄支援以下操作：

• 恢複個別的事務。

• 在 SQL Server 啟動時恢複所有未完成的事務。

• 將還原的資料庫、檔案、檔案組或頁前滾至故障點。

• 支援事務複製。

• 支援高可用性和災難恢複解決方案： AlwaysOn 可用性群組、資料庫鏡像和記錄傳送。

恢複個別的事務

如果應用程式發出 ROLLBACK 語句，或者資料庫引擎檢測到錯誤（例如失去與用戶端的通訊），使用日誌記錄回退未完成的事務所做的修改。

在 SQL Server 啟動時恢複所有未完成的事務

當伺服器發生故障時，資料庫可能處於這樣的狀態：還沒有將某些修改從緩衝寫入資料檔案，在資料檔案內有未完成的事務所做的修改。 啟動 SQL Server 執行個體時，它將對每個資料庫執行恢複操作。 前滾日誌中記錄的、可能尚未寫入資料檔案的每個修改。 在交易記錄中找到的每個未完成的事務都將復原，以確保資料庫的完整性。

將還原的資料庫、檔案、檔案組或頁前滾至故障點

在硬體丟失或磁碟故障影響到資料庫檔案後，可以將資料庫還原到故障點。 先還原上次完整Database Backup和上次差異Database Backup，然後將後續的交易記錄備份序列還原到故障點。

還原每個記錄備份時，資料庫引擎將重新應用日誌中記錄的所有修改，前滾所有事務。 最後的記錄備份還原後，資料庫引擎將使用日誌資訊回退到該點上未完成的所有事務。

支援事務複製

記錄讀取器代理程式程式監視已為事務複製配置的每個資料庫的交易記錄，並將已設複製標記的事務從交易記錄複製到散發資料庫中。 有關詳細資料，請參閱 事務複製的工作原理。

支援高可用性和災難恢複解決方案

待命伺服器解決方案、AlwaysOn 可用性群組、資料庫鏡像和記錄傳送極大程度上依賴於交易記錄。

在 AlwaysOn 可用性群組方案中，資料庫的每個更新（主要複本）在資料庫的完整且獨立的副本（次要複本）中直接再現。 主要複本直接將每個日誌記錄發送到次要複本，這可將傳入日誌記錄應用到可用性群組資料庫，並不斷前滾。 有關詳細資料，請參閱 AlwaysOn 容錯移轉叢集執行個體

在記錄傳送方案中，主伺服器將主要資料庫的活動交易記錄發送到一個或多個目標伺服器。 每個次要伺服器將該日誌還原為其本地的次要資料庫。 有關詳細資料，請參閱 關於記錄傳送。

在資料庫鏡像方案中，資料庫（主體資料庫）的每次更新都在獨立的、完整的資料庫（鏡像資料庫）副本中立即重建。 主體伺服器執行個體立即將每個日誌記錄發送到鏡像伺服器執行個體，鏡像伺服器執行個體將傳入的日誌記錄應用於鏡像資料庫，從而將其繼續前滾。 有關詳細資料，請參閱 資料庫鏡像。

Transaction Log characteristics

SQL Server 資料庫引擎 交易記錄的特徵：

• 交易記錄是作為資料庫中的單獨的檔案或一組檔案實現的。 日誌緩衝與資料頁的緩衝區快取是分開管理的，因此可在資料庫引擎中產生簡單、快速和功能強大的代碼。 有關詳細資料，請參閱交易記錄物理體繫結構。
• 日誌記錄和頁的格式不必遵守資料頁的格式。
• 交易記錄可以在幾個檔案上實現。 通過設定日誌的 FILEGROWTH 值可以將這些檔案定義為自動擴充。 這樣可減少交易記錄內空間不足的可能性，同時減少管理開銷。 有關詳細資料，請參閱 ALTER DATABASE (Transact-SQL)。
• 重用記錄檔中空間的機制速度快且對事務輸送量影響最小。

交易記錄截斷

日誌截斷將釋放記錄檔的空間，以便由交易記錄重新使用。 必須定期截斷交易記錄，防止其佔滿分配的空間（絕對會！）。 幾個因素可能延遲日誌截斷，因此監視日誌大小很重要。 某些操作可以最小日誌量進行記錄以減少其對交易記錄大小的影響。

日誌截斷可從 SQL Server 資料庫的邏輯交易記錄中刪除不活動的虛擬記錄檔，釋放邏輯日誌中的空間以便物理交易記錄重用這些空間。 如果交易記錄從不截斷，它最終將填滿分配給物理記錄檔的所有磁碟空間。

為了避免空間不足，除非由於某些原因延遲日誌截斷，否則將在以下事件後自動進行截斷：

• 簡單復原模式下，在檢查點之後發生。

• 在完整復原模式或大量記錄復原模式下，如果自上一次備份後產生檢查點，則在記錄備份後進行截斷（除非是僅複製記錄備份）。

  有關詳細資料，請參閱本主題後面的 可能延遲日誌截斷的因素。

備忘

日誌截斷並不減小物理記錄檔的大小。 若要減少物理記錄檔的物理大小，則必須收縮記錄檔。 有關收縮物理記錄檔大小的資訊，請參閱 Manage the Size of the Transaction Log File。

Factors that can delay log truncation

在日誌記錄長時間處於活動狀態時，交易記錄截斷將延遲，交易記錄可能填滿，這一點我們在本主題（很長）前面提到過。

[!IMPORTANT} 有關如何響應已滿交易記錄的資訊，請參閱解決交易記錄已滿的問題（SQL Server 錯誤 9002）。

實際上，日誌截斷會由於多種原因發生延遲。 查詢 sys.databases 目錄檢視的 log_reuse_wait 和 log_reuse_wait_desc 列，瞭解哪些因素（如果存在）阻止日誌截斷。 下表對這些列的值進行了說明。

log_reuse_wait 值	log_reuse_wait_desc 值	說明
0	NOTHING	當前有一個或多個可重複使用的虛擬記錄檔。
1	CHECKPOINT	自上次日誌截斷之後，尚未產生檢查點，或者日誌頭尚未跨一個虛擬記錄檔移動。 （所有復原模式） 這是日誌截斷延遲的常見原因。 有關詳細資料，請參閱資料庫檢查點 (SQL Server)。
2	LOG_BACKUP	在截斷交易記錄前，需要進行記錄備份。 （僅限完整復原模式或大量記錄復原模式） 完成下一個記錄備份後，一些日誌空間可能變為可重複使用。
3	ACTIVE_BACKUP_OR_RESTORE	資料備份或還原進行中（所有復原模式）。 如果資料備份阻止了日誌截斷，則取消備份操作可能有助於解決備份直接導致的此問題。
4	ACTIVE_TRANSACTION	事務處於活動狀態（所有復原模式）： 一個長時間啟動並執行事務可能存在於記錄備份的開頭。 在這種情況下，可能需要進行另一個記錄備份才能釋放空間。 請注意，長時間啟動並執行事務將阻止所有復原模式下的日誌截斷，包括簡單復原模式，在該模式下交易記錄一般在每個自動檢查點截斷。 延遲事務。 “延遲交易 ”是有效活動事務，因為某些資源不可用，其復原受阻。 有關導致事務延遲的原因以及如何使它們擺脫延遲狀態的資訊，請參閱延遲交易 (SQL Server)。 長時間啟動並執行事務也可能會填滿 tempdb 的交易記錄。 Tempdb 由使用者事務隱式用於內部對象，例如用於排序的工作表、用於雜湊的工作檔案、遊標工作表，以及資料列版本設定。 即使使用者事務只包括讀取資料（SELECT 查詢），也可能會以使用者事務的名義建立和使用內部對象， 然後就會填充 tempdb 交易記錄。
5	DATABASE_MIRRORING	資料庫鏡像暫停，或者在高效能模式下，鏡像資料庫明顯滯後於主體資料庫。 （僅限完整復原模式） 有關詳細資料，請參閱資料庫鏡像 (SQL Server)。
6	REPLICATION	在事務複製過程中，與發布相關的事務仍未傳遞到散發資料庫。 （僅限完整復原模式） 有關事務複製的資訊，請參閱 SQL Server Replication。
7	DATABASE_SNAPSHOT_CREATION	正在建立資料庫快照集。 （所有復原模式） 這是日誌截斷延遲的常見原因，通常也是主要原因。
8	LOG_SCAN	發生日誌掃描。 （所有復原模式） 這是日誌截斷延遲的常見原因，通常也是主要原因。
9	AVAILABILITY_REPLICA	可用性群組的輔助副本正將此資料庫的交易記錄記錄應用到相應的次要資料庫。 （完整復原模式） 有關詳細資料，請參閱：AlwaysOn 可用性群組概述 (SQL Server)。
10	—	僅供內部使用
11	—	僅供內部使用
12	—	僅供內部使用
13	OLDEST_PAGE	如果將資料庫配置為使用間接檢查點，資料庫中最早的頁可能比檢查點 LSN 早。 在這種情況下，最早的頁可以延遲日誌截斷。 （所有復原模式） 有關間接檢查點的資訊，請參閱資料庫檢查點 (SQL Server)。
14	OTHER_TRANSIENT	當前未使用此值。

可盡量減少日誌量的操作

最小日誌記錄是指只記錄在不支援時間點恢複的情況下恢複事務所需的資訊。 本主題介紹在大量記錄復原模式下（以及簡單復原模式下）按最小方式記錄、但在運行備份時例外的操作。

備忘

記憶體最佳化表不支援最小日誌記錄。

備忘

在完整 復原模式下，所有大容量操作都將被完整地記錄下來。 但是，可以通過將資料庫暫時切換到用於大容量操作的大量記錄復原模式，最小化一組大容量操作的日誌記錄。 最小日誌記錄比完整記錄更為有效，並在大容量事務期間，降低了大規模大容量操作填滿可用的交易記錄空間的可能性。 不過，如果在最小日誌記錄生效時資料庫損壞或丟失，則無法將資料庫恢複到故障點。

下列操作在完整復原模式下執行完整記錄，而在簡單和大量記錄復原模式下按最小方式記錄日誌：

• 大量匯入操作（bcp、BULK INSERT 和 INSERT...SELECT）。 有關在何時對大容量匯入表按最小方式進行記錄的詳細資料，請參閱 Prerequisites for Minimal Logging in Bulk Import。

啟用事務複製時，將完全記錄 BULK INSERT 操作，即使處於大量記錄復原模式下。

• SELECT INTO 操作。

啟用事務複製時，將完全記錄 SELECT INTO 操作，即使處於大量記錄復原模式下。

• 插入或追加新資料時，使用 UPDATE 語句中的 .WRITE 子句部分更新到大型值資料類型。 注意，在更新現有值時沒有使用最小日誌記錄。有關大型值資料類型的詳細資料，請參閱資料類型 (Transact-SQL)。

• 在UPDATETEXT 、 nUPDATETEXT 和 UPDATETEXT, nUPDATETEXT, 、 UPDATETEXT 語句。 注意，在更新現有值時沒有使用最小日誌記錄。

  重要

  不推薦使用 WRITETEXT 語句和 UPDATETEXT 語句，應該避免在新的應用程式中使用這些語句。

• 如果資料庫設定為簡單或大量記錄復原模式，則無論是離線還是聯機執行操作，都會按最小方式記錄一些索引 DDL 操作。 按最小方式記錄的索引操作如下：

  • CREATE INDEX 操作（包括索引檢視表）。

  • ALTER INDEX REBUILD 或 DBCC DBREINDEX 操作。

    重要

    不推薦使用“DBCC DBREINDEX 語句”；請不要在新的應用程式中使用該語句。

  • DROP INDEX 新堆重建（如果適用）。 （ DROP INDEX 操作期間將 始終 完整記錄索引頁的釋放操作。）

 

 

SQL Server事務 交易記錄