Home » SQL » View SQL Transaction Log: Fn_dblog Function vs SysTools Log Analyzer

View SQL Transaction Log: Fn_dblog Function vs SysTools Log Analyzer

👤 Naveen Sharma | Modified: November 17th, 2023|SQL | 6 Minutes Reading

Have you ever thought about how SQL Server logs track the transactions and what information SQL Server saves in the transaction log file?

Well, SQL Server keeps the track of all database changes along with the database transaction.

It is done in a file known as the transaction log or TLOG. Every SQL Server has a particular transaction log and there is at least one TLOG per SQL Server database.

How to View SQL Transaction Log Using Different Ways

As the transaction logs hold very important information about the database, this information cannot be read directly as the logged data is saved in binary, which it is not a human-readable format. Hence, to get the information of logged operations, there are basically two methods:

  1. fn_dblog Function
  2. Third-party tool

Both ways include some pros and cons and in this technical guide, we will disclose all the information about these methods. So, let us begin now.

#1. View SQL Transaction Log Using fn_dblog()

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. Although, it may display in the intelliSense results.

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.


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.

#2. Read SQL Transactions Using an Automated Approach

SQL Server .ldf File Reader is a quick and reliable solution to read and analyze the SQL Server Log file (.ldf) transactions. The software preview all .ldf activities like Transaction, Table Name, Time, Name, and Query. Also, it can read all the transactions such as INSERT, DELETE, UPDATE etc. It quickly scans, display log file and auto locate the associated MDF file.

Download Now Purchase Now

It supports Datetime2, datetimeoffset, hierarchyid, sql_variant, geometry, and geography data types. Via this, one can fetch as well as view the records from the Live database and recover deleted records if the database is in Simple Recovery Mode. The tool supports both online and offline SQL database environment.

Live Working With SQL Log Analyzer

    • Install the software and click Start >> All Programs and open SQL Log Reader

Launch Tool

    • Hit the Open button and add the .ldf file from which you want to read the transactions

hit open button

    • Click OK after completing the scanning process of LDF file

scan complete

    • View the file folders and data items along with database type as well as entries

view data files

    • Sort the transactions on the bases of Transaction, Time, Table Name, and Transaction Name

sort transactions

    • Choose and export the desired Log file from the SQL Server Database and SQL Compatible SQL Scripts or the CSV file type

Select filters

How to Query FN_DBLOG in SQL Server Database?

Now, that our problem is solved, let’s understand more about these T-SQL commands. This can help users to use these whenever they need them.

  • fn_db_log()
SELECT * FROM fn_dblog (
   NULL, -- Start LSN nvarchar(25)
   NULL  -- End LSN nvarchar(25)
  • fn_full_dblog()
SELECT * FROM sys.fn_full_dblog 
  NULL, -- Start LSN nvarchar (25) 
  NULL, -- End LSN nvarchar (25)  
  NULL, -- Database ID int 
  NULL, -- Page file ID int 
  NULL, -- Page ID int 
  NULL, -- Logical Database ID nvarchar (260)
  NULL, -- Backup Account nvarchar (260)
  NULL -- Backup Container nvarchar (260)

More About FN_DBLOG and FN_FULL_DBLOG Functions

If talk about columns, then both the fn_dblog as well as fn_full-dblog have a total of 130 columns in them. Out of these 130 columns, the most common 15 that users take in use are mentioned below.

Column Description
Current LSN Current Log Sequence Number
Previous LSN Previous Log Sequence Number
Operation Operation Description
Context operation’s Context
Transaction ID ID of Transaction in Log File
Log Record Length Row Size in bytes
Page ID Table ID
SP ID User Session ID
Xact ID User Transaction ID
Begin Time Transaction Start Time
End Time Transaction End Time
Number of Locks Total Locks Number
Lock Information Description of Lock
Description T-Log Row Description
Log Record Content of the Transaction in Hexadecimal Values

Now, it’s time to understand the operations columns as it is quite wide. The purpose of this column is to show the type of operation performed in the SQL server & logged in the log file.

Operation Description
LOP_BEGIN_XACT To Begin Transaction
LOP_COMMIT_XACT To End Transaction
LOP_FORMAT_PAGE For Page Modified
LOP_INSERT_ROWS For Row Inserted
LOP_MODIFY_ROW For Row Modified
LOP_MODIFY_COLUMNS For Column Modified
LOP_XACT_CKPT Checkpoint
LOP_BEGIN_CKPT Checkpoint Start
LOP_END_CKPT Checkpoint End

Also Read: Running DBCC Updateusage in SQL Server

If users need to see all of the operations along with their size, they can simply refer to the below-mentioned query:

  • To see all operations & sizes just follow this command
count(*) AS [No of Records],
SUM([Log Record Length]/1024.00/1024.00) AS [RecordSize (MB)]
FROM fn_dblog(NULL,NULL)
GROUP BY Operation
ORDER BY [RecordSize (MB)] DESC

Observational Verdict

The SQL Server saves all its information in the master database where the physical records become separated in .mdf file. The primary database file saves the tables, indexes, schemas etc. All the transactional information of these physical records is saved in a log file, which plays a vital role in SQL server.

It is so because the transaction logs are the most crucial evidence in case of any log error or intruder attacks on the SQL Server that is using activities like SQL injection. Therefore, in this article, we have discussed two different methods to view SQL transaction logs. Now, users can opt any method that seems more secure and reliable for them.