Guide For Reading SQL Server Transaction Logs
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
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?
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)
- 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)
- 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.
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')
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
- Step 2: Click on Open to add .ldf and associated .mdf file
- Step 3: Choose Online or Offline Option to Scan ldf file data to the software
- Step 4: After scanning .ldf file software allow you to preview .ldf file transactions
- Step 5: Sort the transactions as per Transaction, Time, Table Name & Transaction Name
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.