Guide For Reading SQL Server Transaction Logs

admin | November 7th, 2017 | SQL Transaction Log |

Introduction

As we know that the SQL Server stores all its information in the master database where it separates the physical record in .mdf file. Meanwhile the transactional information of these physical records is stored in a log file. The .mdf file stores all the tables, schema, indexes etc. The log file plays a very important role in SQL Server, because the transaction logs are very important evidences in case any transactional error occurs on the Server or if any intruder attacks on it using actions like SQL injection. In this Article we will know, how to view/read SQL Server transaction logs and how they are important for us or for SQL Server database.

Why to Read SQL Server Transaction Log?

Before telling you why we need to read the transaction logs on SQL Server, it is necessary for you to know what type of information is captured by the transaction logs.

Basic Operation:The transaction log captures all the basic DML Operations like INSERT, UPDATE, DELETE and also captures some DDL Operations like CREATE, TRUNCATE, & DROP.

Now, coming to the point, as told before, 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. And this information can be used in forensics to find those records which have been deleted mistakenly or if the size of our log file is growing automatically or lost data needs to be recovered. So the transaction logs are very important assets in such scenarios.

Way of Reading SQL Server Transaction Logs

As we know that the transaction logs are very important in SQL Server so we also need to know the way of reading SQL transaction logs. Although the transaction logs keep very crucial information about the database and generally this information cannot be read directly because the logged data is not stored in human readable format. Thus, to get the information from the logged operations, we use some undocumented functions like fn_dblog () and fn_dump_dblog () which tells the way of reading transaction logs. In this article we will use the first undocumented function, i.e. fn_dblog().

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. The fn_dblog() function accepts two parameters, Start LSN and End LSN.

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?

Below is the step by step working of fn_dblog function.

1. At first we need to create a database

Create database readingtranlog;
GO

2. In second step we need to create a table in this database

Use readingtranlog;
Create table person ( Sn int IDENTITY, Name char (25) );

3. Now in the third step, we can see with the help of fn_dblog() 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.

4. 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

5. 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

6. The sixth step will show the logged transactions which we have been performed in the fifth step.

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.

Consequences of Using fn_dblog ()

  • fn_dblog() function can be useful while user wants to view SQL transaction log.
  • User can also use the undocumented fn_dblog() function to check the transaction log to see the activities like page splits or objects being dropped.
  • One of the most important things is that the fn_dblog() function is an undocumented function, the MS SQL Server allows users to use the function at their own risk.
  • fn_dblog() can be used to view SQL Server transaction log in a backup

Alternate solution to View SQL Server Log File

If we see the functionality of fn_dblog() function, we will get a very effective result from this function because the function works as a SQL Server transaction log explorer and helps to view SQL Server database transaction log file. We can see all the logged activities and the function is also very helpful to determine the SQL INJECTION attacks. But the MS SQL Server estimates to fn_dblog() function as an undocumented function and also this method is too long. Thus, it could prove to be the cause of data Loss. So in such critical points it is highly recommended that users go for a commercial SQL Server Log Explorer tool instead, For reading SQL Server transaction logs instantly.

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
    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

Free Download
100% Secure

Transaction Log Restore