解釋一下SQLSERVER事務(wù)日志記錄

大家知道在完整恢復模式下，SQLSERVER會(huì )記錄每個(gè)事務(wù)所做的操作，這些記錄會(huì )存儲在事務(wù)日志里，有些軟件會(huì )利用事務(wù)日志來(lái)讀取

操作記錄恢復數據，例如：log explorer

那么事務(wù)日志記錄怎麼查看，里面都記錄了些什么？

打開(kāi)可以利用下面SQL語(yǔ)句來(lái)查看所在數據庫的事務(wù)日志記錄

1 USE [GPOSDB] --要查看事務(wù)日志記錄的數據庫2 GO3 SELECT * FROM [sys].[fn_dblog](NULL,NULL)

事務(wù)日志記錄里很多東西可以看的，里面記錄了非常詳細的數據庫活動(dòng)信息

我這里只介紹一些重要的需要知道的字段，其他字段由于本人能力有限而且覺(jué)得其他字段不是很重要就不介紹了

CurrentLSN：當前LSN號，事務(wù)日志中的每個(gè)記錄都由一個(gè)唯一的日志序列號 (LSN) 標識。LSN 是這樣排序的：如果 LSN2 大于 LSN1，

則 LSN2 所標識的日志記錄描述的更改發(fā)生在日志記錄 LSN1 描述的更改之后

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

Operation：當前LSN所做的操作

Context：操作的上下文

TransactoinID：事務(wù)ID號

Log Record Fixed Length：LSN記錄的所占虛擬日志文件的固定長(cháng)度

Previous LSN：前一個(gè)LSN號

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

AllocUnitID：修改的那條數據所屬分配單元ID

AllocUnitName：修改了數據的表名

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

Slot ID：數據所在數據頁(yè)面的第幾條記錄

PartitionID：數據所在數據頁(yè)面的所在分區ID

如上圖，修改數據的表名是Insert_Test，Page ID是0001:00000121 轉換為十進(jìn)制為289  Slot ID是6（即數據頁(yè)的第6條記錄）

通過(guò)下面SQL語(yǔ)句就可以查看頁(yè)面所在數據

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                                            

這個(gè)表只有兩個(gè)字段，我們看一下表數據

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

Checkpoint Begin：Checkpoint開(kāi)始時(shí)間

Checkpoint Begin DB Version：當前數據庫版本 SQL2005是611  SQL2012是706

Checkpoint End：checkpoint的結束時(shí)間，這個(gè)時(shí)間肯定在Checkpoint Begin的下一條事務(wù)日志記錄的位置

Minimum LSN: 這個(gè)第一個(gè)日志記錄的日志序列號 (LSN)，稱(chēng)為最小恢復 LSN (MinLSN)

Dirty Pages：臟的數據頁(yè)

Oldest Replicated Begin LSN：如果數據庫配置復制的話(huà)，那么最老的復制起始LSN

Next Replicated End LSN:下一個(gè)復制結尾LSN

Last Distributed End LSN：最新的分發(fā)結尾LSN

SPID:執行當前操作的進(jìn)程ID

Beginlog Status:開(kāi)始記錄事務(wù)日志的狀態(tài)，這個(gè)狀態(tài)表示現時(shí)能夠正常記錄事務(wù)日志

Begin Time：事務(wù)開(kāi)始時(shí)間

Transaction Name：事務(wù)名稱(chēng)

End Time：事務(wù)結束時(shí)間

Transaction Begin：記錄這個(gè)事務(wù)的begin transaction的時(shí)候的cureent LSN

Master DBID:顯示當前master數據庫的DBID

Preplog Begin LSN:啟動(dòng)數據庫前的前一個(gè)事務(wù)日志LSN

Prepare Time:準備啟動(dòng)數據庫的時(shí)間

New Split Page：哪個(gè)數據頁(yè)產(chǎn)生了頁(yè)拆分

Rows Deleted：數據頁(yè)有多少行被刪除了

Description：描述這個(gè)事務(wù)是干什么的，有時(shí)候事務(wù)名稱(chēng)不一定就是他所做的操作名稱(chēng)，

比如這里碰巧事務(wù)名和描述都是CREATE TABLE 如果你為這個(gè)事務(wù)命名的話(huà)，那么只能看Description列看這個(gè)事務(wù)是做什么的

-------------------------------------------------華麗的分割線(xiàn)-------------------------------------------------------------------

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

Operation：當前LSN所做的操作

Context：操作的上下文

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

-------------------------------------------------------華麗的分割線(xiàn)---------------------------------------------------

在大容量日志恢復模式下，在事務(wù)日志記錄里你會(huì )看不到對數據頁(yè)的操作，當你使用bcp ，bulk inert， select into大容量操作語(yǔ)句的時(shí)候

像下圖的那樣修改數據和插入數據的記錄你在事務(wù)日志記錄里找不到的

所以大容量日志恢復模式時(shí)，ldf文件才這麼小，插入速度才這麼快

下面引用MSDN:

http://msdn.microsoft.com/zh-cn/library/ms190925.aspx
可以盡量減少日志量的操作
“最小日志記錄”是指只記錄在不支持時(shí)間點(diǎn)恢復的情況下恢復事務(wù)所需的信息。 本主題介紹在大容量日志恢復模式下（以及簡(jiǎn)單恢復模式下）按最小方式記錄、但在運行備份時(shí)例外的操作。
 注意
在完整恢復模式下，所有大容量操作都將被完整地記錄下來(lái)。 但是，可以通過(guò)將數據庫暫時(shí)切換到用于大容量操作的大容量日志恢復模式，最小化一組大容量操作的日志記錄。 最小日志記錄比完整日志記錄更為有效，并在大容量事務(wù)期間，降低了大規模大容量操作填滿(mǎn)可用的事務(wù)日志空間的可能性。 不過(guò)，如果在最小日志記錄生效時(shí)數據庫損壞或丟失，則無(wú)法將數據庫恢復到故障點(diǎn)。
下列操作在完整恢復模式下執行完整日志記錄，而在簡(jiǎn)單和大容量日志恢復模式下按最小方式記錄：
大容量導入操作（bcp、BULK INSERT 和 INSERT...SELECT）。 有關(guān)在何時(shí)對大容量導入表按最小方式進(jìn)行記錄的詳細信息，請參閱在大容量導入中按最小方式記錄日志的前提條件。
 注意
啟用事務(wù)復制時(shí)，將完全記錄 BULK INSERT 操作，即使處于大容量日志恢復模式下。
SELECT INTO 操作。
 注意
啟用事務(wù)復制時(shí)，將完全記錄 SELECT INTO 操作，即使處于大容量日志恢復模式下。
插入或追加新數據時(shí)，使用 UPDATE 語(yǔ)句中的 .WRITE 子句部分更新到大型值數據類(lèi)型。 注意，在更新現有值時(shí)沒(méi)有使用最小日志記錄。 有關(guān)大型值數據類(lèi)型的詳細信息，請參閱數據類(lèi)型 (Transact-SQL)。
在 text、ntext 和 image 數據類(lèi)型列中插入或追加新數據時(shí)的 WRITETEXT 和 UPDATETEXT 語(yǔ)句。 注意，在更新現有值時(shí)沒(méi)有使用最小日志記錄。
 注意
不推薦使用 WRITETEXT 語(yǔ)句和 UPDATETEXT 語(yǔ)句，因此應該避免在新的應用程序中使用這些語(yǔ)句。
如果數據庫設置為簡(jiǎn)單或大容量日志恢復模式，則無(wú)論是脫機還是聯(lián)機執行操作，都會(huì )按最小方式記錄一些索引 DDL 操作。 按最小方式記錄的索引操作如下：
CREATE INDEX 操作（包括索引視圖）。
ALTER INDEX REBUILD 或 DBCC DBREINDEX 操作。
 注意
不推薦使用 DBCC DBREINDEX 語(yǔ)句，因此應該避免在新的應用程序中使用該語(yǔ)句。
DROP INDEX 新堆重新生成（如果適用）。
 注意
DROP INDEX 操作期間將始終完整記錄索引頁(yè)的釋放操作。

還可以看一下這篇帖子，關(guān)于大容量日志恢復模式

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）的操作，沒(méi)有記錄數據。。 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模式下，所做的事務(wù)日志備份，是怎么抓取到數據變化的呢？19 20 我做了實(shí)驗，處于Bulk logged模式下，執行了事務(wù)備份，之后還是可以通過(guò)該備份文件來(lái)恢復到select * into 操作之后。21 22 select * into ../....from ......這個(gè)應該是可以最小化日志的操作吧。

帖子里面說(shuō)到，不記錄事務(wù)日志，那么怎麼進(jìn)行數據庫還原呢？實(shí)際上通過(guò)數據庫的系統頁(yè)BCM頁(yè)來(lái)記錄數據頁(yè)的變更的

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

BCM頁(yè)面結構

View Code

 ----------------------------------------------------華麗的分割線(xiàn)-------------------------------------------------

大家有興趣可以看一下我寫(xiě)的這篇文章，更加了解事務(wù)日志

對SQLSERVER數據庫事務(wù)日志的疑問(wèn)

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

根據上面事務(wù)日志中的這幾個(gè)字段，LOG EXPLORER軟件為什麼能恢復數據生成insert腳本，估計是讀取事務(wù)日志中刪除了數據的數據頁(yè)id

然后到數據頁(yè)里先保存下數據，但是如果使用大容量日志恢復模式或者使用truncate table語(yǔ)句估計LOG EXPLORER軟件也救不了你

因為delete語(yǔ)句無(wú)論是where 刪除某條記錄還是全表delete應該都會(huì )每條記錄生成一個(gè)事務(wù)日志記錄,即生成一個(gè)LSN

但是大容量日志恢復模式或者使用truncate table語(yǔ)句是不記錄每行數據的LSN的，可能只記錄刪除動(dòng)作

AllocUnitID：修改的那條數據所屬分配單元ID

AllocUnitName：修改了數據的表名

Page ID：數據頁(yè)ID

Slot ID：數據所在數據頁(yè)面的第幾條記錄

PartitionID：數據所在數據頁(yè)面的所在分區ID

 --------------------------------------------------華麗的分割線(xiàn)-------------------------------------------------------

 當你備份事務(wù)日志并截斷日志之后，然后你沒(méi)有對數據庫做任何操作，但是依然會(huì )看到有事務(wù)日記記錄生成

至于原因，由于篇幅關(guān)系，大家可以到我的另一篇博文的最下面那個(gè)問(wèn)題，里面有答案

為什麼完整備份之后日志記錄會(huì )增加，有時(shí)候備份完之后會(huì )增加，有時(shí)候備份完之后會(huì )減少？？？

因為數據庫啟動(dòng)的時(shí)候一定修改一些系統數據庫頁(yè)所以肯定會(huì )有一些事務(wù)日志記錄生成

文章地址：對SQLSERVER數據庫事務(wù)日志的疑問(wèn)

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

文章有什么不對的地方，大家一定要拍磚哦o(∩_∩)o