View SQL server log Part 1

A friend once asked me if my data was deleted and when it happened without using a third-party tool. SQL Server provides an uninitialized ented function fn_dblog, which allows us to view the active transaction log.

Syntax:

: Fn_dblog (@ startinglsn, @ endinglsn)

If all parameters are null, all transaction information is captured by default.

Using this function, we can query DML and DDL information, such as data deletion, modification, and update. Here is an example of data update:

Create Table Test (namevarchar (10 ))

-- Insert a data entry

Insert into testvalues ('allen test ')

Go 5

--- Query the modification to Table Test

Select [transaction name], operation, allocunitid, allocunitname, [begin time] fromfn_dblog (null, null)

Where allocunitname = 'dbo. test' andoperation = 'lop _ insert_rows'

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

Nullop_insert_rows72057594040090624 DBO. Test null

Nullop_insert_rows72057594040090624 DBO. Test null

Nullop_insert_rows72057594040090624 DBO. Test null

Nullop_insert_rows72057594040090624 DBO. Test null

Nullop_insert_rows72057594040090624 DBO. Test

-- Delete a table

Drop Table Test

---- Query the deletion of a table from the log

Select [transaction name], operation, allocunitid, allocunitname, [begin time] fromfn_dblog (null, null)

Where [transaction name]
= 'Dropobj'

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

Dropobjlop_begin_xact null

-- Query page split

Select Operation, allocunitname, count (*) asnumberofincidents

From: fn_dblog (null, null)

Where operation = n' lop _ delete_split'

Group byoperation, allocunitname

Note: The lsn obtained from fn_dblog cannot be directly used as a parameter. You need to convert the hexadecimal format to numeric:

The lsn we havefrom the log dump abve is 0000009d: 0000021e: 0001.to convert it:

• Take the rightmost 4 characters (2-byte log record number) and convert to a 5-character decimal number, including leading zeroes, to get stringa
• Take the middle number (4-byte log block number) and convert to a 10-character decimal number, including leading zeroes, to get stringb
• Take the leftmost number (4-byte VLF sequence number) and convert to a decimal number, with no leading zeroes, to get stringc
• The lsn string we need is stringc + stringb + stringa

So0000009d: 0000021e: 0001 becomes '000000' + '000000' + '000000' = '000000'. (from sqlskills)

 

You can refer to the following script:

 

Declare @ pageid $ nvarchar (23), @ pageidnvarchar (50 ),
@ Sqlcmdnvarchar (4000 );

Set @ pageid $ = '0001: 0000004d '--- pageid

 

Select @ pageid =

Convert (varchar (4), convert (INT, convert (varbinary,

Substring (@ pageid $, 0, 5), 2 )))

+ ','
+

Convert (varchar (8), convert (INT, convert (varbinary,

Substring (@ pageid $, 6, 8), 2 )))

 

--- View page content

Set @ sqlcmd =
'Dbcc page (''crack _ me'', '+ @ pageid
+ ', 3) with tableresults'

Execute (@ sqlcmd)

In addition, this function can only take effect for the current active log. If the log cannot be read after backup, we need to use another function (Fn_dump_dblog

Http://blog.csdn.net/smithliu328/article/details/7817540) Read from the backup. The above only shows that the data is modified, but the data cannot be recovered.

At present, some scripts on the Internet can be used for restoration. I am currently conducting a test. If the test succeeds, the script will be sent later.

 

Recommended articles:

Database repair Part1: Create your own test upt Database

Database repair Part2: Page restore

Database repair series Part3: repair_allow_data_loss

Database restoration series part4: rebuilding Database Log Files