View SQL Server logs Part 1

Once a friend asked me that the data has been deleted, not the use of third-party tools can check when it happened. SQL Server provides a undocumented function Fn_dblog lets us view the active transaction log.

The syntax is as follows:

:: Fn_dblog (@StartingLSN, @EndingLSN)

If the parameters are null, the default is to fetch all the transaction information.

Using this function we can query DML,DDL information, such as data deletion, updating and so on. Let's look at an example of a data update:

Create table Test(namevarchar )

-- Insert bar Data

INSERT into testvalues(' allen test ')

Go 5

--- query changes to the table Test

SELECT [Transaction Name], Operation, Allocunitid, Allocunitname, [Begin time]fromfn_ DBLog(null,null)

where allocunitname =' dbo.test 'andoperation=' lop_insert_rows '

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

nulllop_insert_rows72057594040090624 dbo.test NULL

nulllop_insert_rows72057594040090624 dbo.test NULL

nulllop_insert_rows72057594040090624 dbo.test NULL

nulllop_insert_rows72057594040090624 dbo.test NULL

nulllop_insert_rows72057594040090624 Dbo.test

-- Delete table

Drop Table Test

---- Query The deletion of a table from Log

SELECT [Transaction Name], Operation, Allocunitid, Allocunitname, [Begin time]fromfn_ DBLog(null,null)

where [Transaction Name] = ' dropobj '

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

Dropobjlop_begin_xact NULL 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 found from Fn_dblog cannot be used directly as a parameter, and it is necessary to convert the 16 binary into numeric:

The LSN we havefrom the log dump above 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 leads ING zeroes, to get Stringa
• Take the middle number (4-byte log block number) and convert to a 10-character decimal number, including leading Zeroe s, 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 ' 157 ' + ' 0000000542 ' + ' 00001 ' = ' 157000000054200001 '. (from Sqlskills)

You can refer to the following script:

DECLARE @pageID $ NVARCHAR, @pageIDNVARCHAR(@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 on the currently active log, and if it cannot be read after log backup, we need to use another function (fn_dump_dblog http://blog.csdn.net/smithliu328/article/ details/7817540) is read from the backup. It is only possible to see the data being modified, but it is not possible to recover the data.

Now someone on the internet has made some scripts that can be recovered, and I am now doing a test, and if I can succeed, I will send out the script later.

Reproduced in: http://blog.csdn.net/kevinsqlserver/article/details/7800628

"Explaining SQL Server transaction log records"

--select * from Fn_dblog (null,null)

View SQL Server logs Part 1