Database

SQL Server fn_dblog() Function Details and Example

November 12, 2012

SQL Server fn_dblog() Function Details and Example

The fn_dblog() function (formerly known as the DBCC command) is one of several undocumented functions for SQL Server. It allows you to view the transaction log records in the active part of the transaction log file for the current database.

Note that use of the fn_dblog() function (and all other undocumented commands) against a production database instance is executed at your own risk.

fn_dblog() Parameters

The fn_dblog() function accepts two parameters:

  • The first is starting log sequence number, or LSN. You can also specify NULL, which means it will return everything from the start of the log.
  • The second is the ending LSN. You can also specify NULL, which means you want to return everything to the end of the log.

The fn_dblog() function is fairly simple. Use the fn_dblog() function as shown below in Example 1 to get info from the transaction log.

fn_dblog() Example 1

This basic fn_dblog() function statement will return details from the transaction log:

SELECT * FROM fn_dblog(NULL, NULL)

 

It is easy to get more specific info from the SQL Server transaction log as well. See Example 2 below.

fn_dblog() Example 2

You can use fn_dblog to examine the transaction log to look for activities such as page splits or objects (such as tables or indexes) being dropped.

This sample statement will find all log records associated with dropped objects:

SELECT * FROM fn_dblog(NULL, NULL) WHERE [Transaction Name] = ‘DROPOBJ’

 

You can also examine the results to track parent and child objects, as shown in Example 3.

fn_dblog() Example 3

The following query will return results from fn_dblog():

SELECT [Current LSN], [Operation], [Context], [Transaction ID], [AllocUnitName], [Page ID], [Transaction Name], [Parent Transaction ID], [Description] FROM fn_dblog(NULL, NULL)

 

When the [Parent Transaction ID] value is not NULL, you can see the actions that initiated the log activity by filtering for that [Transaction ID] value. For example, let’s say that I find a [Parent Transaction ID] of 0000:00004b1d. I can then run this command to see what triggered that event (with special thanks for the inspiration to http://killspid.blogspot.com/2006/07/using-fndblog.html):

SET NOCOUNT ON DECLARE @LSN NVARCHAR(46) DECLARE @LSN_HEX NVARCHAR(25) DECLARE @trx_id NVARCHAR(28) = ‘0000:00004b1d’ 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) WHERE [Transaction ID] = @trx_id) 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) SELECT [Current LSN], [Operation], [Context], [Transaction ID], [AllocUnitName], [Page ID], [Transaction Name], [Parent Transaction ID], [Description] FROM fn_dblog(@LSN_HEX, NULL)

 

The fn_dblog() command is a very effective tool that can provide essential info regarding changes to data in the SQL Server database. However, it is an undocumented command. As such, you will want to be careful about incorporating this command into any application because Microsoft may change the nature and structure of this command without notice.

 


Thomas LaRock is a Head Geek at SolarWinds and a Microsoft® Certified Master, Microsoft Data Platform MVP, VMware® vExpert, and former Microsoft Certified Trainer. He has over 20 years’ experience in the IT industry as a programmer, developer, analyst, and database administrator. LaRock has spent much of his career focused on data and database administration, which led to his being chosen as a Technical Evangelist for Confio Software in 2010, where his research and experience helped to create the initial versions of the software now known as SolarWinds® Database Performance Analyzer. LaRock has served on the board of directors for the Professional Association for SQL Server® (PASS), and is an avid blogger, author, and technical reviewer for numerous books about SQL Server management. He now focuses on working with customers to help resolve problems and answer questions regarding database performance tuning and virtualization for SQL Server, Oracle®, MySQL®, SAP®, and DB2®. He’s made it his mission to give IT and data professionals longer weekends.