Analyzing SQL database transaction log using sys.fn_dblog( )

We all know that SQL Server transaction logs contains all the information about any changes made to a database and SQL server uses this information for recovery during any roll back.

Sometimes we feel whether we can view information inside the transaction log. Many of us may not be aware that SQL server has provided us some ways to look into the database truncation log. We can use sys.fn_dblog( ) to look into any database log file.

We should remember that sys.fn_dblog() is undocumented hence we need to use it with caution . Also remember sys.fn_dblog( ) can be used to look into only the active portion of the truncation log. So if the log backup is done the log will clear and we can’t use the function to look into the log.  The truncations in the log backups can be viewed using fn_dump_dblog(): which I have discussed in earlier article

Let’s follow the example and explore the sys.fn_dblog( ).

Let’s first Create a new database:

USE [master];

GO

SET NOCOUNT ON;

Go

CREATE DATABASE [TestFNDB];

GO

USE [TestFNDB];

GO

USE [master]

GO

ALTER DATABASE [TestFNDB] SET RECOVERY FULL WITH NO_WAIT

GO

Now Let’s create one tables:

Use [TestFNDB]

GO

CREATE TABLE [ConfigTable] (

    [ValueID] INT IDENTITY,

    [Value] CHAR (25) );

 Let’s insert some records:

INSERT INTO [ConfigTable] ([Value]) VALUES('One');

GO

Verify that the table exists and data are there

 

Now let’s update a record

Update [dbo].[ConfigTable] set Value ='Two' where ValueID=1

Now let’s see how to use fn_dblog()

SELECT

    [Current LSN],

    [Operation],

    [Context],

    [Transaction ID],

       [RowLog Contents 0],

       [RowLog Contents 1],

    [Description]

FROM

    fn_dblog (NULL, NULL),

    (SELECT

        [Transaction ID] AS [trnid]

    FROM

        fn_dblog (NULL, NULL)

    WHERE

        [Transaction Name] LIKE '%UPDATE%') [trnm]

WHERE

    [Transaction ID] = [trnm].[trnid];

My Output:

In the above output the row Operation LOP_MODIFY_ROW contains the actual data values in Column [RowLog Contents 0], [RowLog Contents 1] in varbinary

We need to convert the value in [RowLog Contents 0] (0x4F6E65) to varchar. This gives the original value.

When we convert [RowLog Contents 1] (0x54776F) to varchar. This gives the updated value.

 So using fn_dblog() we were able to explore the database transaction log and look at the value that was updated in one of the table. This was simplified view of the database transaction log since I had only one update running against one table.

You can also get details such as Transaction ID , Transaction SID , Current LSN etc. Use of some of the columns can be done during recovery which I have described in another article for fn_dump_dblog() . Please experimenting with fn_dblog() on your own and share any new things you find.

Hoping the article will be useful.