Read the Transaction Logs in SQL Server

INTRODUCTION

In this tutorial we will know about, how to read a transaction log in SQL server and also we will learn all the reading methods of a transaction log. From this tutorial you will know why MS SQL permits you to read the transaction logs. As we know very well about the transaction log, but for a consideration, I will give you a small introduction of transaction log, thereafter we will further discuss about it.

What is Transaction Log in SQL Server?

The Transaction log is a technique to keep your records secure in SQL server. The transaction log is a kind of backup for records through which, we can recover our database from a disaster. Also the transaction log is used to identify those records which are mistakenly deleted by the user. Without the transaction log a user cannot perform any task in SQL server.

From this small introduction, we learned how much a transaction log is important for us. So, now coming over to the topic, let us know more about transaction log.

What Events Captured In The Transaction Log?

Except some events, the transaction log captures all the important events like:

• Begin or End the transaction.
• All the insert, delete and update events.
• Creation of indexes and tables
• Drop events.
• All the page allocation and De-allocations.
• All the locks and truncation of tables.

These above events are captured in the transaction log but there are also some events such as select into, bulk insert bcp..etc. These logged minimally and depend on recovery model of the database.

Why Is It Important To Capture A Transaction?

As we know that the database is quite vulnerable to corruption. As the data is critically important for the user, it is equally important for the database. So keeping in mind that the database goes into a stable state where we cannot recover it, the SQL server captures the transaction to recover the data so, it can be used as a backup, if necessary.

The second reason is that, if a user accidentally deletes a database, the captured transaction can be used to recover the deleted data.

Note: Although the recovery of the data totally depends on recovery models and also depends on user, i.e. which recovery model has been fixed for the database.

How to Read A Transaction Log In SQL Server?

Microsoft doesn’t provide any tool to read the transaction logs, but it does provide some undocumented functions using which a user can read all the transaction logs. But Microsoft doesn’t support these undocumented functions, rather it gives a warning: “use these functions at your own risk”.

Here are some problems in fn_db_dumplog. Microsoft’s SQL team is aware of this problem and will fix it in future. These undocumented functions show what transaction logs captures. Here is an undocumented function fn_dblog, which helps you to read the transaction logs.

Let’s see how an undocumented fn_dblog function works in SQL server.

Reading Transactions using fn_dblog function

Following steps tells, how we can read the transaction logs in SQL server:

1. Create database readtranlog;
        GO

In the first step I have created a database & named it readtranlog.

2. Use readtranlog;
        GO
        Create table persons
        (sn int IDENTITY,
        Name CHAR (25)
        );

After that in second step I have created a table named persons.

3. Use readtranlog;
        GO
        Select count(*) from fn_dblog(null,null)

In the third step we can check what steps and processes have been used by the SQL server to create both; the database and table.

We can see that there are number of processes that have been generated for just creating a database and a table.

4. Use readtranlog;
        GO
        select [current lsn],
        [operation],
        [transaction name],
        [transaction id],
        [transaction sid],
        [spid],
        [begin time]
        From  fn_dblog(null,null)

In the fourth step i have shown a few records but we can see what data is available in the transaction log file. There are some important columns (Transaction Name) in the fourth step, which shows the database name as well as the table name. The [Transaction Id] is same for all the [Current LSN].

5. Use readtranlog
        GO
        Insert into persons values (1, ‘mike’);
        GO 
        Update persons
        Set Name =’peter’
        Where Name =’mike’
        GO
        Delete persons
        Where sn=’1’
        GO

In the fifth step we perform insert, update and delete operations to check how these transactions are logged in the database log file.

6. USE readtranlog;
        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')

In last, sixth step, it shows that how these three transactions are logged in the transaction log file.

Conclusion

The above six steps illustrate how we can use the fn_dblog function to read a transaction log in SQL server. We can detect those transactions which were accidentally deleted and also detect those transactions which we do not know about. But it is a too long and time consuming process and the fn_dblog function can only read and show the log data, but it cannot decode the log data so it is difficult to read the output of fn_dblog function. Because Microsoft does not deliver any log reader tools so it is good to have an SQL log reader tool by which transaction logs can easily be read and also be used for forensics.