用sql語句dbcc log 檢視SQL Server 資料庫的事務日誌

用sql語句dbcc log 檢視SQL Server 資料庫的事務日誌
 
1)用系統函式

select   *   from fn_dblog(null,null)

2)用DBCC

dbcc log(dbname,4)       --(n=0,1,2,3,4)

1 - 更多資訊plus flags, tags, row length

2 - 非常詳細的資訊plus object name, index name,page id, slot id

3 - 每種操作的全部資訊

4 - 每種操作的全部資訊加上該事務的16進位制資訊

預設 type = 0

要檢視MSATER資料庫的事務日誌可以用以下命令：

DBCC log (master)

Current LSN             Operation                       Context                         Transaction ID LogBlockGeneration   Tag Bits Log Record Fixed Length Log Record Length Previous LSN            Flag Bits Log Reserve Description                                                                                                                                                                                                                                                      Log Record
----------------------- ------------------------------- ------------------------------- -------------- -------------------- -------- ----------------------- ----------------- ----------------------- --------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0005f4f8:00000413:0001  LOP_BEGIN_CKPT                  LCX_NULL                        0000:00000000  0                    0x0000   96                      96                0005f4f8:000003fc:0001  0x0000    0                                                                                                                                                                                                                                                                            0x00006000F8F40500FC03000001000000000000000000960058DE1001D89D000000000000000000000000950220D9AE09000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0005f4f8:00000414:0001  LOP_END_CKPT                    LCX_NULL                        0000:00000000  0                    0x0000   136                     136               0005f4f8:00000413:0001  0x0000    0           log_minRecoveryLsn 0005f4f8:00000413:0001;log_replbeginlsn 00000000:00000000:0000;log_replnextlsn 00000000:00000000:0000;log_distbackuplsn 00000000:00000000:0000;log_distlastlsn 00000000:00000000:0000                                                         0x00008800F8F40500130400000100000000000000000099005ADE1001D89D0000F8F405001304000001009502000000000900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0005f4f8:00000415:0001  LOP_BEGIN_XACT                  LCX_NULL                        0000:09aed921  0                    0x0000   64                      164               00000000:00000000:0000  0x0002    9098        ALTER DATABASE OPTION STATE CHAN;0x0105000000000005150000002ec85adcf4db7058c411b231e8030000                                                                

0x00003600F8F40500180400000400020024D9AE09000007080001270001000100CF010000F0F40500D8620000E001000191B901000000010000000000
0005f4f8:00000418:0006  LOP_MODIFY_ROW                  LCX_PFS                         0000:09aed924  0                    0x0000   62                      88                0005f4f8:00000418:0005  0x0002    194         0x00--&gt00 0001:002ccc89;0x00--&gt00 0001:002ccc8a;0x00--&gt00 0001:002ccc8b;0x00--&gt00 0001:002ccc8c;0x00--&gt00 0001:002ccc8d;0x00--&gt00 0001:002ccc8e;0x00--&gt00 0001:002ccc8f     

 

很懷念LogExplor
這個工具，到目前還沒有解析SQL2008的版本

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8183550/viewspace-671480/，如需轉載，請註明出處，否則將追究法律責任。