Posts Tagged ‘SQL SERVER – Undocumented function – fn_dblog’

fn_dblog is one of my favorite undocumented functions; I use it to design multiple recovery solutions. Today, I will discuss its purpose, syntax, parameters and one of the problems that I faced with respect to its parameters.

Purpose :
It gives you the detail view of the transaction log.


    fn_dblog (@start,@end)

It accepts two parameters, given below are the details.
@start : Start LSN but it must be in the integer format.
@end : End LSN but it must be in the integer format.

Let me explain it with simple examples :
Example 1: (With default parameters)

Select * from fn_dblog(NULL,NULL)


Example 2: (Passed LSN)
Lets take a current LSN from above example and pass it as a parameter.The purpose to pass a single LSN is to pick up only one transaction log record.

use test
Select * from fn_dblog('000005c9:0000133d:0022','000005c9:0000133d:0022')

Msg 9005, Level 16, State 3, Line 1
Either start LSN or end LSN specified in OpenRowset(DBLog, …) is invalid.

Ooops…… I am unable to execute it.

The reason behind the above error message is that I just picked up LSN from above result set and passed it into fn_dblog.
Whereas the correct way to do it is, first convert the LSN to integer and then pass it to fn_dblog.

Given below is the script that will convert your hexa decimal LSN into integer LSN.

Declare @Xml Xml
Declare @String Varchar(Max)
Declare @delimiter Varchar(5)
Set @delimiter=':'
Set @String ='000005c9:0000133d:0022'
SET @Xml = cast(('<a>0x'+replace(@String,@delimiter,'</a><a>0x')+'</a>')
           AS XML)

;With CTE as (SELECT A.value('.', 'varchar(max)') as [Column]
from @Xml.nodes('a') AS FN(A) )
Select Stuff((Select ':' +
Convert (varchar(max),Convert(INT,cast('' AS XML).value
('xs:hexBinary(substring(sql:column("[Column]"),3) )', 'varbinary(max)')))
from CTE for xml path('') ),1,1,'') as [Current LSN]


Lets pass the above output (1481:4925:34) to the fn_dblog and view the result set.

use test
Select * from fn_dblog('1481:4925:34','1481:4925:34')


Now, you can see that fn_dblog filter worked and it picked up only one LSN.

Conclusion :
No doubt fn_dblog is one of the helpful undocumented functions but do not use this function in the production server unless otherwise required. Also, remember to convert the hexa decimal to integer before passing it to fn_dblog as a parameter.

Read Full Post »