Home > News, SQL Server 2005 > Using fn_dblog

Using fn_dblog


I like the fn_dblog function even if it is undocumented. It enables you to read from you transaction log which contains very valuable information about stuff that is happening in your database. It is for example the only way I now at this time to determin which object experienced a page split if you search for the LOP_DELETE_SPLIT operation.

SELECT *
FROM ::fn_dblog(NULL, NULL)
WHERE operation = ‘LOP_DELETE_SPLIT’

This is the usage of that system function you see everywhere, just passing NULL’s for the @start and @end parameters, which could limit the LSN’s (Log Sequence Number) to be searched. It took me some time to figure it out because just passing the output of the function as a parameter simply doesn’t work. You can’t use a LSN like for example 00000073:000020fa:0001 as a value for the parameters, you have to translate the hexadecimal values into its numeric equivalent (115:8442:1).

Let’s show this with some code and because there’s no easy way to make this conversion, I came up with something quick & dirty. At last an example of the actual usage of the parameters of fn_dblog on the web 😉

SET NOCOUNT ON

DECLARE @LSN NVARCHAR(46)
DECLARE @LSN_HEX NVARCHAR(25)
DECLARE @tbl TABLE (id INT identity(1,1), i VARCHAR(10))
DECLARE @stmt VARCHAR(256)

SET @LSN = (SELECT TOP 1 [Current LSN] FROM fn_dblog(NULL, NULL))
PRINT @LSN

SET @stmt = ‘SELECT CAST(0x’ + SUBSTRING(@LSN, 1, 8) + ‘ AS INT)’
INSERT @tbl EXEC(@stmt)
SET @stmt = ‘SELECT CAST(0x’ + SUBSTRING(@LSN, 10, 8) + ‘ AS INT)’
INSERT @tbl EXEC(@stmt)
SET @stmt = ‘SELECT CAST(0x’ + SUBSTRING(@LSN, 19, 4) + ‘ AS INT)’
INSERT @tbl EXEC(@stmt)

SET @LSN_HEX = (SELECT i FROM @tbl WHERE id = 1) + ‘:’ + (SELECT i FROM @tbl WHERE id = 2) + ‘:’ + (SELECT i FROM @tbl WHERE id = 3)
PRINT @LSN_HEX

SELECT *
  FROM ::fn_dblog(@LSN_HEX, NULL)

.

Source: Killspid (http://killspid.blogspot.com)

Reference: Aleksey Fomchenko (https://sqlconsulting.wordpress.com)

.

Advertisements
Categories: News, SQL Server 2005 Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

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

%d bloggers like this: