SQL Server – ::fn_dblog – Approx Date time

Background

Found myself trying to map fn_dblog LSN Column to actual transaction’s datetime. but ended up down the fruitless fig tree road.

Work Around

Quite a number of the records logged in the ::fn_dblog table, do not have begin / end time. One possible workaround is to use datetime records of proximate records.

Code

Sample Code



; with cte
as
	(
		select
			  [currentLSN] = tblDBLog.[Current LSN]
			, [Operation]
			, [AllocUnitId]
			, [beginTime] = [Begin Time]
			, [RowNumber]
				= ROW_NUMBER() 
					OVER(
						ORDER BY tblDBLog.[Current LSN] asc
					    )
		from   ::fn_dblog(null, null) tblDBLog
	) 
select  

	  [Row Number]
		= tblDBLog.[RowNumber]

	,   [Operation]
		= tblDBLog.[Operation]

	, [schema]
		= object_schema_name(tblObject.object_id)

	, [table]
		= tblObject.name

    , tblDBLog.[CurrentLSN]

	, [LSN]
		= cast(convert(int, convert(varbinary, substring(tblDBLog.[CurrentLSN], 1, 8), 2)) as varchar)
			+ ':' 
			+ cast(convert(int, convert(varbinary, substring(tblDBLog.[CurrentLSN], 10, 8),2)) as varchar)
			+ ':' 
			+ cast(convert(int, convert(varbinary, substring(tblDBLog.[CurrentLSN], 19, 4), 2))  as varchar)

	, [beginTimeForPreviousOperation]				

		= cast(tblDBLogPrevious.[beginTime] as datetime)

	, [PreviousOperation]
		= tblDBLogPrevious.[Operation]

	, [beginTimeForNextOperation]				

		= cast(tblDBLogNext.[beginTime] as datetime)

	, [NextOperation]
		= tblDBLogNext.[Operation]

from  cte tblDBLog

left outer join cte tblDBLogPrevious

	on    tblDBLog.RowNumber > tblDBLogPrevious.[RowNumber]

	and   tblDBLogPrevious.[RowNumber] =
		(

			select max(tblDBLog_Inner.[RowNumber])

			from   cte tblDBLog_Inner

			where  tblDBLog_Inner.[RowNumber]
					< tblDBLog.[RowNumber]

			and    tblDBLog_Inner.[beginTime] is not null

		)


left outer join cte tblDBLogNext

	on tblDBLog.RowNumber < tblDBLogNext.[RowNumber] and tblDBLogNext.[RowNumber] = ( select min(tblDBLog_Inner.[RowNumber]) from cte tblDBLog_Inner where tblDBLog_Inner.[RowNumber] > tblDBLog.[RowNumber]

			and    tblDBLog_Inner.[beginTime] is not null

		)

INNER JOIN sys.allocation_units tblAllocationUnit

	  ON tblDBLog.AllocUnitId = tblAllocationUnit.allocation_unit_id

INNER  JOIN sys.partitions tblPartition

  ON tblAllocationUnit.container_id = tblPartition.partition_id

INNER  JOIN sys.objects tblObject

      ON tblPartition.object_id  = tblObject.object_id

where tblDBLog.Operation = N'LOP_DELETE_SPLIT'

order by 
	tblDBLog.[RowNumber]



Output:

proximateRecords

Explanation:

  1. In the code pasted above, we are looking for Page Splits, LOP_DELETE_SPLIT

 

More

As part of our extracted columns, we included a computed column LSN.

That column should be passed to the ::fn_dblog to get records within specific beginning and ending LSN.

fn_dbLog

Get Info on fn_fbLog

Syntax


exec sp_help fn_dblog

 

Output:

fn_dblog

 

To use:


    select *
    from ::fn_dblog('61295:95:24', '61296:16:254') tblDBLog

 

Output:

fn_dblogUse

Other Options?

sys.fn_cdc_map_lsn_to_time

MS SQL Server v2008 introduces Change Data Control.  When CDC is enabled, one can map CDC’s LSN to datetime.

We tried passing ::fn_dblog’s LSN to sys.fn_cdc_map_lsn_to_time, but no help…

Code

Sample



declare @LSN varchar(30)
declare @LSN_2 varchar(30)

declare @startLSN varbinary(10)
declare @startLSN_2 varbinary(10)

set @LSN = '61295:95:24'
set @startLSN = cast(@LSN as varbinary(10))

set @LSN_2 = '0000ef6f:0000005f:0018'
set @startLSN_2 = cast(@LSN_2 as varbinary(10))

SELECT sys.fn_cdc_map_lsn_to_time(@startLSN)

SELECT sys.fn_cdc_map_lsn_to_time(@startLSN_2)

Output:

sys

One thought on “SQL Server – ::fn_dblog – Approx Date time

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s