SQL Server交易記錄介紹

SQL Server中的資料庫都是由一或多個資料檔案以及一或多個交易記錄檔組成的。

顧名思意，資料檔案主要儲存資料庫的資料，包括資料庫內容結構，資料頁，索引頁等等。那麼交易記錄到底是幹什麼的呢？它主要是用來儲存資料庫修改記錄的，如：

SQL Server的工作原理為什麼這樣呢？為什麼不把資料立刻寫入資料檔案呢？原因很簡單：為了得到更高的效率和效能。資料檔案為了適應新的資料可能會擴充，可能會重新分配頁，分配新空間等等。而日誌都是連續被記錄的，所以記錄交易記錄要快得多。這也就是為什麼我們通過推薦把物理磁碟單獨劃分一區用來儲存交易記錄的原因了，這樣可以使磁碟在讀寫上最大程式的保持自然連續。資料檔案的讀寫有很大的隨機性。

那麼交易記錄到底都存些什麼呢？看下面這個非常簡單的例子：

 

在交易記錄中，資料變化被記錄在一個連續的日誌記錄中，且每一個記錄都有一個編號，叫做日誌序列編號(Log Sequence Number, LSN)。

在交易記錄中，每一個日誌記錄都被儲存在一個虛擬記錄檔中。交易記錄可以有任意多個虛擬記錄檔，數量的多少取決於資料庫引擎，而且每個虛擬記錄檔的大小也不是固定的。

如所示，活動區間(active portion)的日誌就是包含我們事務的地區。這區間就是完整恢複資料庫所需要的。當更多的事務被建立時，活動區間的日誌也會隨著增長。

那麼當CheckPoint被執行時，會發生什麼變化呢？答案是：所有有變化的資料寫到資料檔案中，然後建立一個檢查點記錄(CheckPoint record)。

現在。由事務1，2，3所導致的變化將會被寫到資料檔案中。因為事務3沒有被提交，所以活動區間日誌的範圍變成了從LSN50到LSN52之間。如果使用簡單恢複模型的話，那麼LSN45到LSN49之間地區可以被重用，因為那些記錄已經不再需要了。

當SQL Server把虛擬記錄檔1和2作為可重用地區時，交易記錄也相應被截斷(Truncate)。需要注意的是，物理日誌大小也會隨著變動。如果資料庫運行在完整或是批量日誌恢複模型下，那麼從LSN45到49之間的地區將被刪除(delete)，而且直到交易記錄被備份後，這段地區的空間才會被重用。

那麼當更新的事務被建立時，又會發生什麼呢？在簡單模式下，日誌的起始空間將會被重用。

在完整或是批量日誌恢複模型下，交易記錄的空間則會被擴充。

假如交易記錄是一個固定大小的日誌，那麼在SQL Server2000系統中，你會收到如下錯誤資訊：

Server: Msg 9002, Level 17, State 6, Line 1
The log file for database 'AdventureWorks' is full. Back up the transaction log for the database to free up some log space.

在SQL Server 2005裡面，錯誤會顯示為：

Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'AdventureWorks' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

注意：並不是說運行在簡單復原模式下的資料庫永遠都不會遇到9002的錯誤。如果你有一個很長的、正在啟動並執行、未提交的事務，那麼你的交易記錄依然會被填滿，因為SQL Server不能刪除任何一個已經開始運行之後被建立的日誌。也就是說，活動區間裡面的日誌從事務起始時被記錄，並且已經沒有活動區間可以被刪除或是重用了。

所以，要保持你的交易記錄基本處理一個可管理的範圍：

• 當更改已經被確認或是由於錯誤導致的復原已經完成時，要馬上提交的你事務。
• 如果資料庫運行在完整或是批量日誌恢複模型下的話，要定期備份你的交易記錄

為了找出資料庫中最起始的活動事務，特別是事務起始時間時，就可以使用DBCC OPENTRAN命令，例如：

DBCC OPENTRAN

結果為：

Transaction information for database 'AdventureWorks'.
Oldest active transaction:
SPID (server process ID) : 52
UID (user ID) : 1
Name : user_transaction
LSN : (754:531:1)
Start time : Jul 14 2008 5:43:55:390PM

為了找出每一個資料庫已經使用的日誌空間大小，可以使用DBCC SQLPERF命令：

DBCC SQLPERF(LOGSPACE)

為了找出交易記錄使用了多少虛擬日誌數量，可以使用DBCC LOGINFO命令。它顯示的細節內容就是你當前所串連資料庫的內容，下面就是AdventureWorks資料庫的輸出：

 

從我們可以得到如下資訊：你的交易記錄中有四個虛擬記錄檔（一行一個），且所有虛擬記錄檔包括在一個單一的物理檔案中(FileId=2)。第一，二，三的虛擬記錄檔大小是458752位元，最後一個虛擬記錄檔的大小是712704位元。1~3虛擬檔案從來沒有被使用或是重用過(Status=0), 第四個虛擬記錄檔正在被使用(Status=2)。虛擬記錄檔在物理上的布局具有串連的編號(FSeqNo是遞增的), 實際情況可能與此有所不同。

 

本文翻譯自sqlbackuprestore，更多精彩內容請瀏覽http://www.sqlbackuprestore.com