Guide For Reading SQL Server Transaction Logs

admin | July 21st, 2018 | SQL Transaction Log |

Introduction

SQL Server stores all its physical information in the master database whereas all its transactional information in the logs. 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.

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.

Download Trial Version of SQL Transaction Log Viewer

Free Download
100% Secure

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. This is on the grounds that the logged information is not stored in 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().

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. The 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?

To utilize fn_dblog() work, you need to pursue the well ordered technique clarified in the segment given underneath:

1. To start with, you need to create a database

Create database readingtranlog;
GO

2. In second step,create a table in this database

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

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

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.

Alternate solution to View SQL Server Log File

If we see the functionality of fn_dblog() function, we will get very effective result from this function as it 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 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.

User can opt for a smart and instant solution that 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
    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. User can opt any of the techniques according to his/her choice.

Useful Links

Become a Writer
Do you have flair of writing for SQL Server & other Database related Technology
We welcome the technical post from bloggers & technical writers who can contribute interesting stories about SQL Server on regular basis. If you think you are a good fit, then this SQL Server Explorer is a Platform for you. Stay in Touch with us.