View SQL Transaction Log: Fn_dblog Function vs SysTools Log Analyzer

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.

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.

#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

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

• • Click OK after completing the scanning process of LDF file

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

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

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

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.

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.

SELECT
[Operation],
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.