View SQL Transaction Log: Fn_dblog Function vs SysTools Log Analyzer

Naveen Sharma | Published: September 25, 2018 | SQL |

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.

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.

How to View SQL Transaction Log Using Different Ways

#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. 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
  • 1

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

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

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

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

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

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.