fn_dblog( ) function documentation

Goal:
This post provide some documentation for the fn_dblog() function.

Key Points:
    * view transaction log of a database (if you want to view the transaction log of a log backup, use fn_dump_dblog function instead.)
    * useful for:
          > restore database to a certain point in time
          > find out when an operation (page split, table delete, table truncate...).
   * take 2 parameters, start time and end time.
   * It's not recommended use this function to scan the transaction log of a production database because it can reduce performance due to increase disk I/O.

-- get fn_dblog() definition
sp_helptext 'sys.fn_dblog';

--result
 create function sys.fn_dblog  ( 
  @start   nvarchar (25) = NULL, 
  @end     nvarchar (25) = NULL 
 )    
returns table 
as 
 return select * 
 from OpenRowset (DBLog, @start, @end, NULL, 1, 
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
       NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL )

-- get table result definition.
sp_help 'sys.fn_dblog';

Result.

Column_name	Type	Length	Prec	Scale	Nullable
Current LSN	nvarchar	46			no
Operation	nvarchar	62			no
Context	nvarchar	62			no
Transaction ID	nvarchar	28			no
LogBlockGeneration	bigint	8	19	0	no
Tag Bits	binary	2			no
Log Record Fixed Length	smallint	2	5	0	no
Log Record Length	smallint	2	5	0	no
Previous LSN	nvarchar	46			no
Flag Bits	binary	2			no
Log Reserve	int	4	10	0	no
AllocUnitId	bigint	8	19	0	yes
AllocUnitName	nvarchar	774			yes
Page ID	nvarchar	28			yes
Slot ID	int	4	10	0	yes
Previous Page LSN	nvarchar	46			yes
PartitionId	bigint	8	19	0	yes
RowFlags	smallint	2	5	0	yes
Num Elements	smallint	2	5	0	yes
Offset in Row	smallint	2	5	0	yes
Modify Size	smallint	2	5	0	yes
Checkpoint Begin	nvarchar	48			yes
CHKPT Begin DB Version	smallint	2	5	0	yes
Max XDESID	nvarchar	28			yes
Num Transactions	smallint	2	5	0	yes
Checkpoint End	nvarchar	48			yes
CHKPT End DB Version	smallint	2	5	0	yes
Minimum LSN	nvarchar	46			yes
Dirty Pages	int	4	10	0	yes
Oldest Replicated Begin LSN	nvarchar	46			yes
Next Replicated End LSN	nvarchar	46			yes
Last Distributed Backup End LSN	nvarchar	46			yes
Last Distributed End LSN	nvarchar	46			yes
Server UID	int	4	10	0	yes
SPID	int	4	10	0	yes
Beginlog Status	binary	4			yes
Xact Type	int	4	10	0	yes
Begin Time	nvarchar	48			yes
Transaction Name	nvarchar	66			yes
Transaction SID	varbinary	85			yes
Xact ID	bigint	8	19	0	yes
Xact Node ID	int	4	10	0	yes
Xact Node Local ID	int	4	10	0	yes
End Time	nvarchar	48			yes
Transaction Begin	nvarchar	46			yes
Replicated Records	int	4	10	0	yes
Oldest Active LSN	nvarchar	46			yes
Server Name	nvarchar	258			yes
Database Name	nvarchar	258			yes
Mark Name	nvarchar	66			yes
Master XDESID	nvarchar	28			yes
Master DBID	int	4	10	0	yes
Preplog Begin LSN	nvarchar	46			yes
Prepare Time	nvarchar	48			yes
Virtual Clock	bigint	8	19	0	yes
Previous Savepoint	nvarchar	46			yes
Savepoint Name	nvarchar	66			yes
Rowbits First Bit	smallint	2	5	0	yes
Rowbits Bit Count	smallint	2	5	0	yes
Rowbits Bit Value	binary	1			yes
Number of Locks	smallint	2	5	0	yes
Lock Information	nvarchar	512			yes
LSN before writes	nvarchar	46			yes
Pages Written	smallint	2	5	0	yes
Data Pages Delta	int	4	10	0	yes
Reserved Pages Delta	int	4	10	0	yes
Used Pages Delta	int	4	10	0	yes
Data Rows Delta	bigint	8	19	0	yes
Command Type	int	4	10	0	yes
Publication ID	int	4	10	0	yes
Article ID	int	4	10	0	yes
Partial Status	int	4	10	0	yes
Command	nvarchar	52			yes
Byte Offset	smallint	2	5	0	yes
New Value	binary	1			yes
Old Value	binary	1			yes
New Split Page	nvarchar	28			yes
Rows Deleted	smallint	2	5	0	yes
Bytes Freed	smallint	2	5	0	yes
CI Table Id	int	4	10	0	yes
CI Index Id	smallint	2	5	0	yes
NewAllocUnitId	bigint	8	19	0	yes
FileGroup ID	smallint	2	5	0	yes
Meta Status	binary	4			yes
File Status	binary	4			yes
File ID	smallint	2	5	0	yes
Physical Name	nvarchar	522			yes
Logical Name	nvarchar	258			yes
Format LSN	nvarchar	46			yes
RowsetId	bigint	8	19	0	yes
TextPtr	binary	16			yes
Column Offset	int	4	10	0	yes
Flags	int	4	10	0	yes
Text Size	bigint	8	19	0	yes
Offset	bigint	8	19	0	yes
Old Size	bigint	8	19	0	yes
New Size	bigint	8	19	0	yes
Description	nvarchar	512			no
Bulk allocated extent count	int	4	10	0	yes
Bulk RowsetId	bigint	8	19	0	yes
Bulk AllocUnitId	bigint	8	19	0	yes
Bulk allocation first IAM Page ID	nvarchar	28			yes
Bulk allocated extent ids	nvarchar	1922			yes
RowLog Contents 0	varbinary	8000			yes
RowLog Contents 1	varbinary	8000			yes
RowLog Contents 2	varbinary	8000			yes
RowLog Contents 3	varbinary	8000			yes
RowLog Contents 4	varbinary	8000			yes
Compression Log Type	smallint	2	5	0	yes
Compression Info	varbinary	8000			yes
PageFormat PageType	smallint	2	5	0	yes
PageFormat PageFlags	smallint	2	5	0	yes
PageFormat PageLevel	smallint	2	5	0	yes
PageFormat PageStat	smallint	2	5	0	yes
PageFormat FormatOption	smallint	2	5	0	yes
Log Record	varbinary	8000			no

As you can see there're  many columns the  fn_dblog() returns.
We won't likely need all of them.

--An example how to use sys.fn_dblog function
SELECT
  [Current LSN], [Previous LSN], Operation, Context,
  [Transaction ID], [Transaction Name], [Transaction SID],
  [Transaction Begin], [Begin Time], [End Time],
  [Log Record], [Minimum LSN], 
  [Checkpoint Begin], [Checkpoint End], Description,
  [Page ID], [Dirty Pages] AS dirty_page_number,
  [New Split Page], [Pages Written], [Used Pages Delta],
  [Lock Information], [Number of Locks]
FROM sys.fn_dblog(NULL, NULL);

Paul Randal has a blog post about fn_dblog() and provide some detailed example. You can read it here. 

Credits:
http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/#comment-7872
http://sqlblog.com/blogs/kalen_delaney/archive/2011/03/08/what-gets-logged-for-index-rebuilds.aspx
http://stevestedman.com/tag/fn_dblog