Microsoft Business Intelligence: Reading the Transaction Log File

To read the Log fie we are using fn_dblog () function. fn_dblog() also known as DBBC command is one of the undocumented functions for the SQL Server, which tells the way to view SQL transaction log. Using this one can read the transaction log records in the active part of the log file for the current database.

Note: Since the fn_dblog () command is not documented by Microsoft hence, it is recommended to use it at your own risk.

Parameters for fn_dblog ()

This undocumented function accepts two different parameters:

@start: Start LSN however, it must be in the integer format

@end: End LSN however, it must be in the integer format

One needs to pass the NULL parameter in order to list all the properties integrated with the LDF file.

Syntax:

SELECT * FROM fn_dblog (NULL, NULL)

After executing this command, one can check the properties related to the log file. As this manual technique proves handy to read the transactions but, it is an undocumented command. Hence, users should be careful about incorporating this command line into any application as Microsoft may modify the nature and construction of this function without any notice.

Let’s see the example

Here I am create a new database “Transaction_Log_Demo”.

Database created successfully. Now setting the recovery sample option.

ALTER DATABASE Transaction_Log_Demo SET RECOVERY SIMPLE;

ALTER DATABASE Transaction_Log_Demo SET AUTO_CREATE_STATISTICS OFF;

Now creating a table.

create table customer

(

id bigint identity(1,1) primary key,

cus_name varchar(50),

cus_add varchar(100),

mob varchar(10)

)

Now inserting few records into the table.

insert into customer(cus_name,cus_add,mob) values ('Bagesh','noida','888880XXXX');

insert into customer(cus_name,cus_add,mob) values ('Rajesh','Pune','88XXX0XXXX');

insert into customer(cus_name,cus_add,mob) values ('Mahesh','Chennai','ZZZ880XXXX');

insert into customer(cus_name,cus_add,mob) values ('Mohan','Patna','888880DDDD');

Records are inserted successfully.

select * from fn_dblog(null,null) where Operation='LOP_INSERT_ROWS'

and AllocUnitName like '%customer%';

4 INSERT ROW are created in the logs.

Many more information we will get using this function.

Complete list of the column in this function

SELECT [current lsn],

operation,

context,

[transaction id],

logblockgeneration,

[tag bits],

[log record fixed length],

[log record length],

[previous lsn],

[flag bits],

[log reserve],

allocunitid,

allocunitname,

[page id],

[slot id],

[previous page lsn],

partitionid,

rowflags,

[num elements],

[offset in row],

[modify size],

[checkpoint begin],

[chkpt begin db version],

[max xdesid],

[num transactions],

[checkpoint end],

[chkpt end db version],

[minimum lsn],

[dirty pages],

[oldest replicated begin lsn],

[next replicated end lsn],

[last distributed backup end lsn],

[last distributed end lsn],

[repl min hold lsn],

[server uid],

spid,

[beginlog status],

[xact type],

[begin time],

[transaction name],

[transaction sid],

[parent transaction id],

[oldest active transaction id],

[xact id],

[xact node id],

[xact node local id],

[end age],

[end time],

[transaction begin],

[replicated records],

[oldest active lsn],

[server name],

[database name],

[mark name],

[master xdesid],

[master dbid],

[preplog begin lsn],

[prepare time],

[virtual clock],

[previous savepoint],

[savepoint name],

[rowbits first bit],

[rowbits bit count],

[rowbits bit value],

[number of locks],

[lock information],

[lsn before writes],

[pages written],

[command type],

[publication id],

[article id],

[partial status],

command,

[byte offset],

[new value],

[old value],

[new split page],

[rows deleted],

[bytes freed],

[ci table id],

[ci index id],

newallocunitid,

[filegroup id],

[meta status],

[file status],

[file id],

[physical name],

[logical name],

[format lsn],

[rowsetid],

[textptr],

[column offset],

flags,

[text size],

offset,

[old size],

[new size],

description,

[bulk allocated extent count],

[bulk rowsetid],

[bulk allocunitid],

[bulk allocation first iam page id],

[bulk allocated extent ids],

[vlfs added],

[invalidatecache id],

[invalidatecache keys],

[copyverioninfo source page id],

[copyverioninfo source page lsn],

[copyverioninfo source slot id],

[copyverioninfo source slot count],

[rowlog contents 0],

[rowlog contents 1],

[rowlog contents 2],

[rowlog contents 3],

[rowlog contents 4],

[rowlog contents 5],

[compression log type],

[compression info],

[pageformat pagetype],

[pageformat pageflags],

[pageformat pagelevel],

[pageformat pagestat],

[pageformat formatoption],

[log record]

FROM Fn_dblog(NULL, NULL);

Out of them below are some use full columns

Column name	Description
Current LSN	Current Log Sequence Number
Previous LSN	Previous Log Sequence Number
Operation	Describes the operation performed like Insert , Delete , update, drop etc.
Context	Context of the Operation
Transaction ID	ID of the transaction in the LOG file
Low Record Length	Size of the row in byte
Allocation Unit name	Object name(table or index) against which the operation was performed
Page id	Table/Index page
Spid	User session id
Xact ID	User Transaction ID – Logged only in the LOP_BEGIN_XACT and LOP_COMMIT_XACT
Begin Time	Transaction start time Logged only in the LOP_BEGIN_XACT
End Time	Transaction start time Logged only in the LOP_COMMIT_XACT
Transaction Name	Refers to the type of transaction
Transaction SID	User security identifier
Page id	Have the information about the page id where page stored.
Number of Locks	Number of lock
Lock information	Having the information about lock type.
rowlog contents 0,1,2,3,4,5	Having the actual data in the hexa decimal format

Operations

The ‘Operation’ column indicates the type of operation that has been performed and logged in the transaction log file. Here are some of the them:

Operation	Description
LOP_CREATE_ALLOCCHAIN	New Allocation chain
LOP_SET_FREE_SPACE	Designates that a previously allocated extent has been returned to the free pool.
LOP_LOCK_XACT	Lock
LOP_SHRINK_NOOP
LOP_XACT_CKPT	During the Checkpoint, open transactions were detected.
LOP_INSERT_ROWS	Row Inserted
LOP_PREP_XACT	Transaction is in a 2-phase commit protocol.
LOP_HOBT_DDL
LOP_MODIFY_COLUMNS	Column Modify
LOP_COUNT_DELTA
LOP_HOBT_DELTA
LOP_MODIFY_HEADER	A new data page created and has initialized the header of that page.
LOP_COMMIT_XACT	Commit Transaction
LOP_DELETE_SPLIT	A page split has occurred. Rows have moved physically.
LOP_END_CKPT	Checkpoint end
LOP_BEGIN_XACT	Begin Transaction
LOP_MODIFY_ROW	Row modification as a result of an Update command.
LOP_INSYSXACT
LOP_EXPUNGE_ROWS	Row physically expunged from a page, now free for new rows.
LOP_INVALIDATE_CACHE
LOP_FORMAT_PAGE	Page Modification
LOP_DELETE_ROWS	Row Deleted
LOP_BEGIN_CKPT	Check point start
LOP_ROOT_CHANGE
LOP_SET_BITS	Designates that the DBMS modified space allocation bits as the result of allocating a new extent.