Four ways to filter results of fn_dblog function.

Some of you might know/use SQL Server undocumented function "fn_dblog" to look at SQL Server Database log.

The syntax of it's usage is

SELECT * FROM fn_dblog (NULL, NULL);

That is pretty easy to use if you are in Simple transnational mode and just doing some testing/research, but if you are looking at production database with huge Log file?

Here are 4 ways to filter the results to make research and troubleshooting more manageable:

Solution #1. Copy Log file content into tempdb table or into another database:

SELECT * INTO #tbl_DBLog
FROM fn_dblog (NULL, NULL);
GO
SELECT * INTO tempdb.dbo.tbl_DBLog
FROM fn_dblog (NULL, NULL);
GO
SELECT * INTO TestDB.dbo.tbl_DBLog
FROM fn_dblog (NULL, NULL);
GO

In this sample code I copy content into a temp table, permanent table in temp db and permanent table in TestDB. By doing this you have to be aware of the following:
1. Never copy Transnational Log data into the same database you are doing research in. By doing that you will change the log.
2. Be aware of the size of the data. It might take a long while to extract whole log and the amount of space required for that table will exceed amount of space used by the Log file.

If you went this path you can easily select needed data using WHERE clause.

Solution #2. Extract only needed columns.

Function "fn_dblog" returns about 130 columns. It is obvious you do not need all of them.
You can limit an output by selecting only certain columns:

SELECT [Current LSN]
      ,[Operation]
      ,[Context]
      ,[AllocUnitName]
      ,[Transaction Name]
      ,[Description]
      ,[Begin Time]
      ,[End Time]
      ,[Prepare Time]
      ,[Lock Information]
      ,[Checkpoint Begin]
      ,[Checkpoint End]
      ,[RowLog Contents 0]
      ,[RowLog Contents 1]
      ,[RowLog Contents 2]
      ,[RowLog Contents 3]
      ,[RowLog Contents 4]
      ,[RowLog Contents 5] 
FROM fn_dblog (NULL, NULL);

However, if you have millions and millions records in the log file it still might be not appropriate solution.

Solution #3. Search for particular operation.

For instance you want to search for any changes in your database you can run following script:

SELECT [Current LSN]
      ,[Transaction Name]
      ,[Begin Time]
FROM fn_dblog (NULL, NULL) 
WHERE [Transaction Name] in ('INSERT','DELETE','UPDATE');

In my test DB I've got following:

In that smaller data set you can locate possible transnational troublemaker, but in order to get details, you still need to extract WHOLE log file content to look at the details.

Here comes my last script to extract LSN for ONLY needed transaction:

Solution #4. Extract only needed LSNs.

Would say we need to extract an information associated with an "UPDATE" for LSNs started at "0000004f:00000087:0001". You can just specify Starting and Ending LSNs as "fn_dblog" parameters:

DECLARE
    @SLSNA BIGINT = 0x4f,
    @SLSNB BIGINT = 0x87,
    @SLSNC BIGINT = 0x1,
    @ELSNA BIGINT = 0x4f,
    @ELSNB BIGINT = 0x88,
    @ELSNC BIGINT = 0x1
DECLARE
    @SLSN BIGINT = (@SLSNA * 10000000000 + @SLSNB) * 100000 + @SLSNC,
    @ELSN BIGINT = (@ELSNA * 10000000000 + @ELSNB) * 100000 + @ELSNC
SELECT [Current LSN]
      ,[Operation]
      ,[Context]
      ,[AllocUnitName]
      ,[Transaction Name]
      ,[Begin Time]
      ,[End Time]
      ,[Lock Information]
FROM fn_dblog (@SLSN,@ELSN);

That portion of code would return you ONLY Log records between LSNs "0000004f:00000087:0001" and "0000004f:00000088:0001".

That means you do not have to read entire log into a temp or staging table and do not have to scan entire log again and again  to do your research.