SQL SERVER – Beginning Reading Transaction Log File

In this article we will examine what the SQL Server Transaction Log is, why do we need one and how it works.

Transaction Log Overview

The SQL Server transaction log is maybe one of the most unclear parts of SQL Server architecture. At the same time the transaction log is one of the most essential components of every database.

Every SQL Server database has a transaction log that keeps all transactions and the database changes made by each transaction. In case of a system failure, the transaction log might be required to bring your database back to a consistent state. That is why the transaction log should never be deleted or moved.

Transaction Log Purpose

Let’s have a look at ACID. Atomicity, Consistency, Isolation, and Durability are set of properties that guarantee that database transactions are processed reliably. The transaction log assists us with durability. It guarantees that the committed transactions will stay forever. Durability can be achieved by flushing the transaction’s log records to non-volatile storage before acknowledging commitment. This means that the transaction must be stored on the hard drive. Otherwise, if you got a power failure and your changes were only in the memory on the database server, after the server restart you may find that your changes weren’t saved. Thus, all changes must be written to disk before we can return a successful commit back to the application.

SQL Server stores data rows on 8 KB pages, that can be located anywhere in the data file. A page can hold a big number of rows (about 400 rows on the page). The page is the smallest unit of reading and writing, so if you modify one row on the page, the entire 8 KB page is written back to disk.

Let’s consider the situation when you update 100 rows in a single transaction. Some of these rows could be spread out across the data file.

You would need to drag the disk head to each position, write the updated page to disk, and move onto the next location, waiting for the disk to spin, and the disk head to move each time.

The transaction log is a sequential file, and rather than make a randomly placed record for each page, it capable of writing in 512 byte sectors.

The smallest write should hit one disk sector. A disk sector is a physical sector on a drive. Most drives contain sectors which are 512 bytes.

Based on this, we can conclude that SQL Server will write 512 bytes to the log file for a single tiny write transaction.

Well, as transaction log is sequential, you don’t need to wait for the disk head to move to the next position to write the change. The next position is already right under the disk head. This means you can write the changes down on disk quickly.

While writing changes to the transaction log, the in-memory version of the data page is also being updated. Further, the Lazy Writer process will come and commit these data pages to disk. If you got a power failure, then all the data pages in memory will be lost, but we have a record of all changes that occurred in the database.

Reading Transaction Log File

Let’s examine how SQL Server logs transactions and what information is stored in the transaction log file.

To read the transaction log file we will use the fn_dblog function. This function is one of several undocumented SQL Server functions.

Every record in the SQL Server transaction log is uniquely identified by a log sequence number (LSN). If LSN_n+1 > LSN_n — the change described by the log record referred to LSN_n+1 occurred after the change described by the log record LSN_n.

fn_dblog accepts two parameters:

• The first is starting log sequence number. You can also specify NULL, which means it will return everything from the start of the log.
• The second is the ending log sequence number. You can also specify NULL, which means you want to return everything to the end of the log.

For demonstration purposes let’s create a test database and a table.

The IDE allows to create a database in several clicks. Once you establish a connection to SQL Server, right click the connection, and then click New Database. The Database designer will appear. In the designer window enter the database name (I will use ViewTransactionLog) and click the Update Database button. That is all. To create a table, expand the database node, right click the Table folder, and then click New Table.

The table will contain three columns: ID, FirstName, and LastName.

Now we can use SQL script editor to execute the fn_dblog function.

Note that 461 rows have been created for the database and the blank table. To see transaction log data:

Note that the Transaction Name column contains the database name. This value appears only when the particular transaction starts with “LOP_BEGIN_XACT” (means begin transaction) in the Operation column. The operation column shows which operation is being performed by SQL Server, either insert, update, delete, shrink, lock, page allocation, etc. Transaction ID is the same for all transaction parts.

Now let’s run several DML commands to see how this affect the transaction log file.

Let’s have a look at the transaction log.

We can read the first raw in the log as:

• INSERT statement with transaction ID 0000:00000a80
• started at 2014/10/10 10:44:52:273
• INSERT a row in a HEAP table ‘dbo.PErson’ in pageID 0000011d:00000140
• finished at 2014/10/10 10:44:52:273

Conclusion

As you can see, the transaction log is a list of changes that have occurred in a database. The transaction log also gives us the ability to replay any operation that occurred in the database.

If you take a full database backup, and then have copies of the transaction log, you will be able to replay every operation and bring the database back to a consistent state at any point in time.

For instance, if you accidentally dropped a table, you can bring the database back to the point just before the drop time or by a specific LSN, and recover the data.

Reference: Pinal Dave (https://blog.sqlauthority.com)