MS SQL 事務日誌管理小結

本文是對SQL Server事務日誌的總結，文章有一些內容和知識來源於官方文檔或一些技術博客，本文對引用部分的出處都有標註。

事務日誌介紹

在SQL Server中，事務日誌是資料庫的重要組件，如果系統出現故障，則可能需要使用事務日誌將資料庫恢復到一致狀態。每個SQL Server資料庫都擁有自己的事務日誌，用於記錄所有事務以及每個事務對資料庫所做的修改。那麼資料庫的哪些操作會記錄在事務日誌中呢？具體一點的說，這些操作包括：

·         每個事務的開始和結束。

·         每次數據修改（插入、更新或刪除）。 這包括系統存儲過程或數據定義語言 (DDL) 語句對包括系統表在內的任何表所做的更改。

·         每次分配或釋放區和頁。

·         創建或刪除表或索引。

另外，像SELECT這樣的操作是不會記錄在事務日誌當中的。如果你想對事務日誌記錄信息有一個直觀的認識，那麼你可以在測試環境做一些SELECT、INSERT、UPDATE、DDL等操作，然後使用ApexSQL Log這款工具查看具體的事務日誌記錄信息。

USE YourSQLDba;

GO

CREATE TABLE dbo.TEST(ID  INT);

GO

INSERT INTO dbo.TEST SELECT 100;

GO

SELECT * FROM dbo.TEST;

GO

UPDATE dbo.TEST SET ID=101;

GO

DELETE FROM dbo.TEST WHERE ID=101;

GO

如上所示，像DDL、DML操作都會記錄在事務日誌當中，但是SELECT是不會記錄在事務日誌當中（當然SELECT INTO除外，其實SELECT INTO在事務日誌裡面轉化為了CREATE TABLE形式）。另外，需要註意: 事務日誌並不是審計跟蹤。也就是說事務日誌並不能完全替代審計跟蹤。它不提供對資料庫做出改變的審計跟蹤；它不保持對資料庫已執行命令的記錄，只有數據如何改變的結果。其實很多對事務日誌瞭解不深入的人都以為事務日誌可以代替審計跟蹤（曾經有項目經理想讓我從事務日誌當中挖掘出誰誤刪了數據，其實事務日誌只會記錄那個賬號刪除了記錄，並不會記錄客戶端信息，所以不能定位到誰刪除了數據）。如下所示，我們多了一個DROP TABLE操作。你會看到跟上面不一樣的結果。

USE YourSQLDba;

GO

CREATE TABLE dbo.TEST(ID  INT);

GO

INSERT INTO dbo.TEST SELECT 100;

GO

SELECT * FROM dbo.TEST;

GO

UPDATE dbo.TEST SET ID=101;

GO

DELETE FROM dbo.TEST WHERE ID=101;

GO

DROP TABLE dbo.Test;

GO

這篇博客transactionlog中有一張圖，描述了一個更新操作的流程中，事務日誌在這個流程中的位置以及作用。想必看過這張圖後，大家在大腦中會對事務日誌的功能作用有一個初步的形象認識。

其實這張圖還包含了很多隱藏的重要信息，下麵我們一一來述說一下：

預寫式日誌(Write-Ahead Logging）

什麼是預寫式日誌呢？ 其實其核心思想就是在變化的數據寫入到資料庫之前，將相關日誌記錄信息先寫入到日誌. SQL Server的預寫式日誌（Write-Ahead Logging）機制保證修改的描述（例如日誌記錄）會在數據本身修改寫入數據文件前寫入，會寫入磁碟上的事務日誌文件。它是SQL Server保證事務持久性（Durability）的基本機制。一個日誌記錄會包含已提交事務或未提交事務的詳細信息，在數據被事務修改的不同情況下，可能已經寫入數據文件或還沒來得及寫入數據文件，這取決於檢查點是否已發生。

淺談SQL Server中的事務日誌(二)----事務日誌在修改數據時的角色 這篇博客有深入淺出的介紹（如下所示）：

Write-Ahead Logging的核心思想是:在數據寫入到資料庫之前，先寫入到日誌.

因為對於數據的每筆修改都記錄在日誌中，所以將對於數據的修改實時寫入到磁碟並沒有太大意義，即使當SQL Server發生意外崩潰時，在恢復(recovery)過程中那些不該寫入已經寫入到磁碟的數據會被回滾(RollBack),而那些應該寫入磁碟卻沒有寫入的數據會被重做(Redo)。從而保證了持久性(Durability)。

     但WAL不僅僅是保證了原子性和持久性。還會提高性能.

     硬碟是通過旋轉來讀取數據,通過WAL技術，每次提交的修改數據的事務並不會馬上反映到資料庫中，而是先記錄到日誌.在隨後的CheckPoint和Lazy Writer中一併提交,如果沒有WAL技術則需要每次提交數據時寫入資料庫......

官方文檔SQL Server 事務日誌體繫結構和管理指南介紹如下（個人對翻譯做了一下調整，也增加了一點點內容）：<