Guide For Reading SQL Server Transaction Logs & View All Retrieved Data

Stacy Brown ~ Modified: December 26th, 2023 ~ SQL Server 2016, SQL Transaction Log ~ 7 Minutes Reading

sql server transaction log reader

SQL Server stores all its physical information in the master database whereas all its transactional information in the logs. In addition, the .mdf file stores all database objects like tables, schema, indexes etc.

The log file plays an important role in SQL Server as transaction logs are important evidences in case if any transnational error occurs on the Server or if any intruder attacks on it using actions like SQL injection. Therefore, we are going to execute the command for learning how to retrieve data from log files in SQL Server for the purpose of reading SQL server transaction logs.

Hence, in this article, we will talk about how to view/read SQL Server transaction logs and how are they important for SQL Server database. Also, users can use the SQL server transaction log reader software as well.

Download Trial Version of SQL Transaction Log Viewer

Download Now Purchase Now

Why to Read SQL Server Transaction Log?

Before revealing to you why we need to read the transaction logs on SQL Server, it is necessary for you to know what kind of data is caught by the transaction logs.

Basic Operation:The transaction log captures all the fundamental DML Operations like INSERT, UPDATE, DELETE and furthermore captures DDL Operations like CREATE, TRUNCATE, and DROP

Presently, getting to the heart of the matter, reading SQL Server transaction logs is important because the transaction logs contain all the information about the transactions which we have performed on our database. Moreover, this information can be used in forensics:

  • To find those records which have been erased erroneously or;
  • If the size of our log file is growing automatically or;
  • Lost information need to be recuperated

Thus, the transaction logs are very important assets in such scenarios.

Way of Reading SQL Server Transaction Logs

Transaction logs play an important role in SQL Server, one should know the way of reading SQL transaction logs. Despite the fact that the transaction logs keep exceptionally pivotal data about the database and this data cannot be read directly. Evidently, this is on the grounds that the logged information is not stored in a human-readable format.

Thus, to get the data from the logged operations, some undocumented functions like fn_dblog () and fn_dump_dblog () are used. These undocumented functions tell the method of reading transaction logs. In this article we will use the first undocumented function, i.e. fn_dblog() to execute SQL server view transaction log operation.

Advantages of Using fn_dblog()

  • The user can use the undocumented fn_dblog() function to check the transaction log to see the activities like page splits or objects being dropped.
  • fn_dblog() can be used to view SQL Server transaction log in a backup

How to View SQL Transaction log using fn_dblog()?

The fn_dblog() is an undocumented function of SQL server, which provides the facility of reading SQL Server transaction logs. Moreover, this fn_dblog() function accepts two parameters, Start LSN and End LSN.

The fn_dblog() function can be applied as:

NULL is equally passed within the parameters to see all the available entries.

The fn_dblog() function can be applied as:

SELECT * FROM fn_dblog(NULL,NULL)

How to Use fn_dblog() Function?

Here are the Steps to Read SQL Server Transaction Log:

  • To start with, you need to create a database
Create database readingtranlog;
GO
  • In second step,create a table in this database
Use readingtranlog;
Create table person ( Sn int IDENTITY, Name char (25) );
  • In the third step, with the assistance of fn_blog() function, how many steps and processes have taken by the SQL Server to create the database and table.
Use readingtranlog;
Select count(*) from fn_dblog(null,null)

read SQL Server transaction logs

  • We can see the 254 number of steps are taken by the SQL Server for the creation of tables and database.
  • Now in the fourth step we will see what data is available in the log file
Use readingtranlog
GO
select [current lsn],
[operation],
[transaction name],
[transaction id],
[transaction sid],
[spid],
[begin time]
From fn_dblog(null,null)

DML operations

  • In the fifth step we will perform some INSERT, DELETE, UPDATE operations to determine how these operations are logged in the transaction log file.
Use readingtranlog
GO
Insert into person values (‘andrew’);
GO 
Update person
Set Name =’alan’
Where Name =’andrew’
GO
Delete person
Where sn=’1’
GO
  • The sixth step will show the logged transactions which we have been performed in the fifth step. This is the final step to view files without the SQL transaction log reader tool.
USE readingtranlog;
GO
SELECT
[current lsn],
[transaction id],
[operation],
[transaction name],
[context],
[allocunitname],
[page id],
[slot id],
[begin time],
[end time],
[number of locks],
[lock Information]
from sys.fn_dblog(NULL,NULL)
where operation IN 
('LOP_INSERT_ROWS','LOP_MODIFY_ROW',
'LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT')

SQL captured transaction log explore

We can see in above steps how fn_dblog() function shows the captured transaction logs and made the transactions in human readable format.

SQL Server View Transaction Log Using fn_dump_dblog()

If users have online or backup transaction files, the fn_dump_dblog() helps them get the desired solution in case they can’t use the fn_dblog() command. Users need to specify all 63 parameters to execute this function. Moreover, using DEFAULT for the parameters can be helpful in obtaining all the transaction logs.

SELECT * FROM fn_dump_dblog
(NULL,NULL,NULL,NULL,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT, DEFAULT)

Here, users must note that this function returns a table with a total of 129 columns by default. This is a great alternative for users if they don’t want to view transaction log SQL Server Management Studio way.

An advantage of using fn_dump_dblog() is that it allows you to restore your database to a specific point in time using the WITH STOPBEFOREMARK statement. This enables the DB admins to rectify transaction errors or undo changes that affect the database performance.

This fn_dump_dblog() has several advantages. Below are these advantages mentioned:

  • Using the Stopbeforemark attribute, users can recover SQL Database from a specific point.
  • Helps database administrators to fix all the existing errors of the server.
  • Also, contribute to undoing the changes that negatively impact the business.

Alternate Solution to Read Transaction Log SQL Server

If we see the functionality of fn_dblog() function, we will get very effective results from this function as it works as a SQL Server transaction log explorer and helps to view SQL Server database transaction log file.

Now , we can see all the logged activities and the function helpful in determining the SQL INJECTION attacks. But MS SQL Server estimates fn_dblog() function as an undocumented function and also the method of reading is too long. Thus, it could prove to be the cause of data loss.

Users can opt for a smart and instant solution which is SQL Server Log Explorer Tool to view SQL Server transaction logs without any hassle.

Know How to View SQL Server Transaction Log with Commercial Utility

Step-1: Open & launch the SQL Server Transaction Log Reader

SQL Transaction Log Reader

Step-2: Click on Open to add .ldf and associated .mdf file for SQL server view transaction log task.

Open to add .ldf file

Step-3: Choose Online or Offline Option to Scan ldf file data to the software

Choose Online or Offline Option

Step-4: After scanning .ldf file software allow you to preview .ldf file transactions

Preview SQL Transaction logs

Step-5: Sort the transactions as per Transaction, Time, Table Name & Transaction Name

Sort Database Transactions

Summing Up

In this blog, we have clarified the significance of SQL Server transaction logs. Besides, we have additionally acquainted two diverse ways to view and read the transaction logs. Users can opt for any of the techniques according to his/her choice. Hence, we hope that now users can easily execute their task of reading SQL server transaction logs without any hassles at all.