(轉)解釋一下SQLSERVER交易記錄記錄

標籤：

本文轉載自樺仔的部落格http://www.cnblogs.com/lyhabc/archive/2013/07/16/3194220.html解釋一下SQLSERVER交易記錄記錄

大家知道在完整復原模式下，SQLSERVER會記錄每個事務所做的操作，這些記錄會儲存在交易記錄裡，有些軟體會利用交易記錄來讀取

操作記錄恢複資料，例如：log explorer

那麼交易記錄記錄怎麽查看，裡面都記錄了些什嗎？

開啟可以利用下面SQL語句來查看所在資料庫的交易記錄記錄

1 USE [GPOSDB] --要查看交易記錄記錄的資料庫2 GO3 SELECT * FROM [sys].[fn_dblog](NULL,NULL)

 

交易記錄記錄裡很多東西可以看的，裡面記錄了非常詳細的資料庫活動資訊

我這裡只介紹一些重要的需要知道的欄位，其他欄位由於本人能力有限而且覺得其他欄位不是很重要就不介紹了

CurrentLSN：當前LSN號，交易記錄中的每個記錄都由一個唯一的記錄序號 (LSN) 標識。LSN 是這樣排序的：如果 LSN2 大於 LSN1，

則 LSN2 所標識的日誌記錄描述的更改發生在日誌記錄 LSN1 描述的更改之後

MSDN解釋：http://msdn.microsoft.com/zh-cn/library/ms190411(v=SQL.90).aspx

Operation：當前LSN所做的操作

Context：操作的上下文

TransactoinID：事務ID號

Log Record Fixed Length：LSN記錄的所佔虛擬記錄檔的固定長度

Previous LSN：前一個LSN號

--------------------------------------------------------------------------------------------------------------

AllocUnitID：修改的那條資料所屬配置單位ID

AllocUnitName：修改了資料的表名

Page ID：0001:00000121 轉換成十進位：289    所以查看pageid為289頁  DBCC PAGE([pratice],1,289,3)

Slot ID：資料所在資料頁面的第幾條記錄

PartitionID：資料所在資料頁面的所在分區ID

如，修改資料的表名是Insert_Test，Page ID是0001:00000121 轉換為十進位為289  Slot ID是6（即資料頁的第6條記錄）

通過下面SQL語句就可以查看頁面所在資料

1 USE [pratice]2 GO3 DBCC TRACEON(3604,-1)4 GO5 6 DBCC PAGE([pratice],1,289,3)  7 GO

 1 Slot 6 Offset 0x552 Length 211 2  3 Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP      4 Memory Dump @0x0A2AC552 5  6 00000000:   1000d000 3f080000 61616120 20202020 †....?...aaa               7 00000010:   20202020 20202020 20202020 20202020 †                          8 00000020:   20202020 20202020 20202020 20202020 †                          9 00000030:   20202020 20202020 20202020 20202020 †                         10 00000040:   20202020 20202020 20202020 20202020 †                         11 00000050:   20202020 20202020 20202020 20202020 †                         12 00000060:   20202020 20202020 20202020 20202020 †                         13 00000070:   20202020 20202020 20202020 20202020 †                         14 00000080:   20202020 20202020 20202020 20202020 †                         15 00000090:   20202020 20202020 20202020 20202020 †                         16 000000A0:   20202020 20202020 20202020 20202020 †                         17 000000B0:   20202020 20202020 20202020 20202020 †                         18 000000C0:   20202020 20202020 20202020 20202020 †                         19 000000D0:   0200fc†††††††††††††††††††††††††††††††...                      20 21 Slot 6 Column 0 Offset 0x4 Length 422 23 id = 2111                            24 25 Slot 6 Column 1 Offset 0x8 Length 20026 27 name = aaa                                            

這個表只有兩個欄位，我們看一下表資料

--------------------------------------------------------------------------------------------------------

Checkpoint Begin：Checkpoint開始時間

Checkpoint Begin DB Version：當前資料庫版本 SQL2005是611  SQL2012是706

Checkpoint End：checkpoint的結束時間，這個時間肯定在Checkpoint Begin的下一條交易記錄記錄的位置

Minimum LSN: 這個第一個日誌記錄的記錄序號 (LSN)，稱為最小恢複 LSN (MinLSN)

Dirty Pages：髒的資料頁

Oldest Replicated Begin LSN：如果資料庫配置複製的話，那麼最老的複製起始LSN

Next Replicated End LSN:下一個複製結尾LSN

Last Distributed End LSN：最新的分髮結尾LSN

SPID:執行當前操作的進程ID

Beginlog Status:開始記錄交易記錄的狀態，這個狀態表示現時能夠正常記錄交易記錄

Begin Time：事務開始時間

Transaction Name：事務名稱

End Time：事務結束時間

Transaction Begin：記錄這個事務的begin transaction的時候的cureent LSN

Master DBID:顯示當前master資料庫的DBID

Preplog Begin LSN:啟動資料庫前的前一個交易記錄LSN

Prepare Time:準備啟動資料庫的時間

New Split Page：哪個資料頁產生了頁面分割

Rows Deleted：資料頁有多少行被刪除了

Description：描述這個事務是幹什麼的，有時候事務名稱不一定就是他所做的操作名稱，

比如這裡碰巧事務名和描述都是CREATE TABLE 如果你為這個事務命名的話，那麼只能看Description列看這個事務是做什麼的

-------------------------------------------------華麗的分割線-------------------------------------------------------------------

 現在解釋一下 一些常見operation和context，一些不常見的我也不知道 ，呵呵o(∩_∩)o

Operation：當前LSN所做的操作

Context：操作的上下文

DCM頁的資料：http://www.cnblogs.com/lyhabc/archive/2013/01/21/2870392.html

Operation	Context	解釋
LOP_SET_BITS	LCX_DIFF_MAP	設定位元影像，資料： 差異(Differential)備份:只備份上次完整備份後，做修改的部分。備份單位是區(Extent)。意味著某個區內即使只有一頁做了變動，則在差異備份裡會被體現.差異備份依靠一個BitMap進行維護，一個Bit對應一個區，自上次完整備份後，被修改的區會被置為1，而BitMap中被置為1對應的區會被差異備份所備份。而到下一次完整備份後，BitMap中所有的Bit都會被重設為0 而這個BitMap在資料庫第7頁： DCM頁 差異變更(Differential Changed Map，DCM)頁面他跟蹤一個檔案中的哪一個區在最新一次完整Database Backup之後被修改過。SQLSERVER用在增量備份時只對已發生資料變更的分區進行增量備份即可
LOP_BEGIN_XACT		事務開始
LOP_MODIFY_ROW	LCX_HEAP	修改堆表中的某一行記錄
LOP_PREP_XACT		準備啟動資料庫
LOP_COMMIT_XACT		提交事務
LOP_MODIFY_ROW	LCX_BOOT_PAGE	修改資料庫啟動頁
LOP_MODIFY_HEADER	LCX_PFS	修改PFS頁的頁頭部資訊
LOP_INSERT_ROWS	LCX_CLUSTERED	插入資料到叢集索引的索引頁
LOP_INSERT_ROWS	LCX_INDEX_LEAF	插入資料到索引的葉子節點即資料頁
LOP_FORMAT_PAGE	LCX_CLUSTERED	重新組織叢集索引
LOP_DELETE_SPLIT	LCX_CLUSTERED	刪除叢集索引表的一行記錄引起頁面分割
LOP_MODIFY_HEADER	LCX_HEAP	修改堆表的某頁的頁頭資訊
LOP_BEGIN_CKPT	LCX_NULL	檢查點開始
LOP_END_CKPT	LCX_NULL	檢查點結束
LOP_SET_FREE_SPACE	LCX_PFS	修改PFS頁設定那個資料頁是閒置
LOP_ROOT_CHANGE	LCX_CLUSTERED	叢集索引的根節點改變
LOP_INSERT_ROWS	LCX_HEAP	插入資料到堆表

 

 

LOP_FORMAT_PAGE

LCX_HEAP

格式化堆裡的資料頁

Operation	Lock Information	解釋
LOP_LOCK_XACT	HoBt 0:ACQUIRE_LOCK_SCH_M METADATA: database_id = 14   STATS(object_id = 7, stats_id = 11)	在事務裡擷取鎖

                     

-------------------------------------------------------華麗的分割線---------------------------------------------------

在大量記錄復原模式下，在交易記錄記錄裡你會看不到對資料頁的操作，當你使用bcp ，bulk inert， select into大容量動作陳述式的時候

像的那樣修改資料和插入資料的記錄你在交易記錄記錄裡找不到的

所以大量記錄復原模式時，ldf檔案才這麽小，插入速度才這麽快

下面引用MSDN:

http://msdn.microsoft.com/zh-cn/library/ms190925.aspx
可以盡量減少日誌量的操作
“最小日誌記錄”是指只記錄在不支援時間點恢複的情況下恢複事務所需的資訊。 本主題介紹在大量記錄復原模式下（以及簡單復原模式下）按最小方式記錄、但在運行備份時例外的操作。
 注意
在完整復原模式下，所有大容量操作都將被完整地記錄下來。 但是，可以通過將資料庫暫時切換到用於大容量操作的大量記錄復原模式，最小化一組大容量操作的日誌記錄。 最小日誌記錄比完整記錄更為有效，並在大容量事務期間，降低了大規模大容量操作填滿可用的交易記錄空間的可能性。 不過，如果在最小日誌記錄生效時資料庫損壞或丟失，則無法將資料庫恢複到故障點。
下列操作在完整復原模式下執行完整記錄，而在簡單和大量記錄復原模式下按最小方式記錄：
大容量匯入操作（bcp、BULK INSERT 和 INSERT...SELECT）。 有關在何時對大容量匯入表按最小方式進行記錄的詳細資料，請參閱在大容量匯入中按最小方式記錄日誌的前提條件。
 注意
啟用事務複製時，將完全記錄 BULK INSERT 操作，即使處於大量記錄復原模式下。
SELECT INTO 操作。
 注意
啟用事務複製時，將完全記錄 SELECT INTO 操作，即使處於大量記錄復原模式下。
插入或追加新資料時，使用 UPDATE 語句中的 .WRITE 子句部分更新到大型值資料類型。 注意，在更新現有值時沒有使用最小日誌記錄。 有關大型值資料類型的詳細資料，請參閱資料類型 (Transact-SQL)。
在 text、ntext 和 image 資料類型列中插入或追加新資料時的 WRITETEXT 和 UPDATETEXT 語句。 注意，在更新現有值時沒有使用最小日誌記錄。
 注意
不推薦使用 WRITETEXT 語句和 UPDATETEXT 語句，因此應該避免在新的應用程式中使用這些語句。
如果資料庫設定為簡單或大量記錄復原模式，則無論是離線還是聯機執行操作，都會按最小方式記錄一些索引 DDL 操作。 按最小方式記錄的索引操作如下：
CREATE INDEX 操作（包括索引檢視表）。
ALTER INDEX REBUILD 或 DBCC DBREINDEX 操作。
 注意
不推薦使用 DBCC DBREINDEX 語句，因此應該避免在新的應用程式中使用該語句。
DROP INDEX 新堆重建（如果適用）。
 注意
DROP INDEX 操作期間將始終完整記錄索引頁的釋放操作。

還可以看一下這篇文章，關於大量記錄復原模式

http://social.msdn.microsoft.com/Forums/zh-CN/958febc2-5eaf-46e4-b658-4bea087c0b0f

 

 1 0 2 投票 3 另外，做了下測試。 4  5 在Bulk logged模式下，日誌中僅記錄對Page（比如IAM，PFS和GAM）的操作，沒有記錄資料。。 6  7 Operation                          Context                   AllocUnitName 8 LOP_MODIFY_ROW        LCX_PFS        dbo.SomeTable 9 LOP_SET_BITS               LCX_IAM        dbo.SomeTable10 LOP_SET_BITS               LCX_GAM        dbo.SomeTable11 LOP_MODIFY_ROW        LCX_PFS        dbo.SomeTable12 LOP_SET_BITS                LCX_IAM        dbo.SomeTable13 LOP_SET_BITS                LCX_GAM        dbo.SomeTable14 LOP_MODIFY_ROW        LCX_PFS        dbo.SomeTable15 LOP_SET_BITS                LCX_IAM        dbo.SomeTable16 但是對於處於Full模式下的DB，除了包含這部分資料，還有記錄Data的部分。17 18 那處於Bulk Logged模式下，所做的交易記錄備份，是怎麼抓取到資料變化的呢？19 20 我做了實驗，處於Bulk logged模式下，執行了事務備份，之後還是可以通過該備份檔案來恢複到select * into 操作之後。21 22 select * into ../....from ......這個應該是可以最小化日誌的操作吧。

文章裡面說到，不記錄交易記錄，那麼怎麽進行資料庫還原呢？實際上通過資料庫的系統頁BCM頁來記錄資料頁的變更的

參考資料：SQL Server 2008 儲存結構之DCM、BCM

BCM頁面結構

 1 DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理員聯絡。 2  3 PAGE: (1:7) 4  5  6 BUFFER: 7  8  9 BUF @0x03EDB9F410 11 bpage = 0x170E4000                   bhash = 0x00000000                   bpageno = (1:7)12 bdbid = 5                            breferences = 1                      bUse1 = 1409813 bstat = 0xc00009                     blog = 0x32159                       bnext = 0x0000000014 15 PAGE HEADER:16 17 18 Page @0x170E400019 20 m_pageId = (1:7)                     m_headerVersion = 1                  m_type = 1721 m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x022 m_objId (AllocUnitId.idObj) = 99     m_indexId (AllocUnitId.idInd) = 0    Metadata: AllocUnitId = 648806423 Metadata: PartitionId = 0            Metadata: IndexId = 0                Metadata: ObjectId = 9924 m_prevPage = (0:0)                   m_nextPage = (0:0)                   pminlen = 9025 m_slotCnt = 2                        m_freeCnt = 6                        m_freeData = 818226 m_reservedCnt = 0                    m_lsn = (0:0:1)                      m_xactReserved = 027 m_xdesId = (0:0)                     m_ghostRecCnt = 0                    m_tornBits = 028 29 Allocation Status30 31 GAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED           PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL32 DIFF (1:6) = CHANGED                 ML (1:7) = NOT MIN_LOGGED            33 34 ML_MAP: Header @0x09C2C064 Slot 0, Offset 9635 36 status = 0x0                         37 38 ML_MAP: Extent Alloc Status @0x09C2C0C239 40 (1:0)        - (1:43256)    = NOT MIN_LOGGED                              41 42 43 DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理員聯絡。

View Code

 

 ----------------------------------------------------華麗的分割線-------------------------------------------------

大家有興趣可以看一下我寫的這篇文章，更加瞭解交易記錄

對SQLSERVER資料庫交易記錄的疑問

 http://www.cnblogs.com/lyhabc/archive/2013/06/10/3130856.html 

根據上面交易記錄中的這幾個欄位，LOG EXPLORER軟體為什麽能恢複資料產生insert指令碼，估計是讀取交易記錄中刪除了資料的資料頁id

然後到資料頁裡先儲存下資料，但是如果使用大量記錄復原模式或者使用truncate table語句估計LOG EXPLORER軟體也救不了你

因為delete語句無論是where 刪除某條記錄還是全表delete應該都會每條記錄產生一個交易記錄記錄,即產生一個LSN

但是大量記錄復原模式或者使用truncate table語句是不記錄每行資料的LSN的，可能只記錄刪除動作

AllocUnitID：修改的那條資料所屬配置單位ID

AllocUnitName：修改了資料的表名

Page ID：資料頁ID

Slot ID：資料所在資料頁面的第幾條記錄

PartitionID：資料所在資料頁面的所在分區ID

 --------------------------------------------------華麗的分割線-------------------------------------------------------

 當你備份交易記錄並截斷日誌之後，然後你沒有對資料庫做任何操作，但是依然會看到有事務日記記錄產生

至於原因，由於篇幅關係，大家可以到我的另一篇博文的最下面那個問題，裡面有答案

為什麽完整備份之後日誌記錄會增加，有時候備份完之後會增加，有時候備份完之後會減少？？？

因為資料庫啟動的時候一定修改一些系統資料庫頁所以肯定會有一些交易記錄記錄產生

文章地址：對SQLSERVER資料庫交易記錄的疑問

(轉)解釋一下SQLSERVER交易記錄記錄