As part of this Transaction Log Internals series, In this article we are going to observe the use of fn_dblog and some of its important columns.

fn_dblog is an undocumented function introduced in SQL Server 2005. This function is used to return active (or un-truncated) part
of transaction log file.

Syntax:
fn_dblog({BeginLSN | NULL}, {EndLSN | NULL})

Note:
1) LSN in T-Log is in 3 part Hexa-decimal format. but in this function, we have to pass LSN in 3 part decimal format.
For example: if Hex LSN is 0000001a:0000001b:0001 then convert this as decimal an pass it as 00000026:00000027:0001

2) NULL to return all T-Log records (*within its range)

Examples:

select * from fn_dblog(NULL,  NULL) -- returns all available T-Log records

select * from fn_dblog(NULL, '00000026:00000027:0009') -- returns all available T-Log records upto LSN '00000026:00000027:0009'

select * from fn_dblog('00000026:00000027:0009', NULL) -- returns all T-Log records starting from LSN '00000026:00000027:0009'

select * from fn_dblog('00000026:00000027:0001','00000026:00000027:0009') -- returns T-Log records between these LSNs

fn_dblog function has 116 columns (SQL 2008 R2). Just try below command and check how many columns are familiar to you. 

sp_help fn_dblog

Now Let us create a test database named 'Explore_TLog' to understand some important columns of fn_dblog function.
As of now, let us not worry about recovery models and truncate logs. Focus is only to understand important columns of fn_dblog.

-- Note: Below script is tested in SQL Server 2008 R2

USE MASTER
GO

-- Drop if DB is already created
DROP DATABASE EXPLORE_TLOG
GO

-- Create database
CREATE DATABASE EXPLORE_TLOG
GO

-- Use it
USE EXPLORE_TLOG
GO

-- Select *some important columns* of fn_dblog.

SELECT
 [Previous LSN],
 [Current LSN],
 [Transaction ID],
 [Operation],
 [CONTEXT],
 [AllocUnitName],
 [Page ID],
 [Slot ID],
 [Offset in Row],
 [Transaction Name],
 [Begin Time],
 [End Time],
 [Number of Locks],
 [Lock Information],
 [RowLog Contents 0],
 [RowLog Contents 1],
 [RowLog Contents 2],
 [RowLog Contents 3],
 [RowLog Contents 4],
 [Log Record],
 [Log Record Fixed Length],
 [Log Record Length]
FROM fn_dblog(null,null)
GO
-- this function returns 63 records in a newly created database.

Let us observe the output columns in pieces.

 Previous LSN, Current LSN and Transaction Id:

Description:

In my previous article I had mentioned that T-Log records are linked with one common transaction Id.
You are seeing four T-Log records belongs to Transaction Id 267. 
In this example second Log record has first LSN as its Previous LSN, third LSN has second LSN as its previous LSN and so on.
Note that "Previous LSN" value could be 00000000:00000000:0000 or some other LSN according to the activity.

 Columns 4 to 9:

Description:

Operation - Action type. Most of the Operation types are self explanatory. Below link has lots of operation types and its uses.
http://www.sqlservercentral.com/blogs/hugo/2012/11/06/another-dive-into-transaction-log-file-forensics/

Some common operation types:

LOP_BEGIN_XACT -- Begining of transaction

LOP_INSERT_ROWS -- Insert
LOP_MODIFY_ROW -- Update
LOP_DELETE_ROWS -- Delete

LOP_ABORT_XACT  -- Rollback
LOP_COMMIT_XACT  -- Commit

Just Issue below query to know operations recorded in available T-Log portion.
SELECT DISTINCT Operation FROM fn_dblog(NULL, NULL)

Operation, Context, AllocationUnitName, PageNo, SlotNo, RowOffset 
All above columns are meaningful only when you try to read all above columns as single unit of work. 
Here are some examples.

Important Notes: 

1) Allocation Unit is not a table. It is just a unit of table. If you are new to allocation units, read references suggested in part 1.

2) You can find lots of *unfamiliar* system table/view names in AllocationUnitName columns.
For example:
sys.sysschobjs.clst  is one of the allocation unit name. remove third part (.clst) to get actual object name sys.sysschobjs

but If you query the object you will get "Invalid object name" error.
select * from sys.sysschobjs -- sysobjects equivalent

Trick: These system objects listed are protected. you have to login as DAC (connect as Admin:<instance>) and try the query again.
You will get the output. you can see some system objects (in different name) which are similar to sysobjects, syscolumns, sysindexes etc.

Transaction Name, Begin Date and End Date columns:
Note: This output is formatted and filtered. Transaction Id and Operation columns are used here better readability.

Description:
Every Transaction in T-Log ideally will begin (Operation LOP_BEGIN_XACT) with  a transaction name and Begin Time.
And Transactions ideally ends (Operation COMMIT_XACT or ABORT_XACT) With End Time.
These values will be NULL in remaining T-Log records within a transaction.

LockCount and LockDescription:

Description:
Self explainatory. Number of locks acuqired to perform this operation and Actual Lock Informations (DBId, Object, Object HoBT Id, Lock type etc)
These locks informations are needed as the same locks might be required to apply during recovery phase(?).

Row Log Content 0 to 4:

Description:

What you are seeing (in hexadecimal format) here is the actual imacted record in data Page.
For example,
This could be an INSERTED record - In column [Record content 0]
This could be an UPDATED record - Record before update will be stored in [Record content 0] and after update will be stored in [Record content 1]

Log Record:

Description:

Actual log record. the master/mystery piece. Format/anatomy of a log record is un-documented. 
But if you observe this column closely you can find [RowLog Contents] column in other words change made by DDL/DML will be part of it.

In our next artice, We are going to perform some simple DDL and DML operations and observe this log columns.

References:

Paul Randal
http://www.sqlservercentral.com/blogs/hugo/2012/11/06/another-dive-into-transaction-log-file-forensics/