What is SQL Server Transaction Log

SQL Server Transaction Log is the log file with .ldf extension. Every transaction that occurs in SQL Server and database modifications performed by these transactions are stored in transaction log file.

Each record in the Transaction Log file has a unique number. This is called LSN (Log Sequence Number). LSN2 is always larger than LSN1. When restoring Transaction Logs, you can see the LSN in the message section.

To understand the architecture of SQL Server you need to understand the importance of the Transaction Log. Each database has at least one transaction log file.

Multiple Transaction Log files and performance impact?

More than one transaction log file can be added but only 1 log file is written at the same time.

In other words, creating a transaction log file on a different disk to improve performance is useless.

But sometimes, when there is no space on the disk where the transaction log file is located, we can solve this problem by creating a transaction log file on another disk.

Where is SQL Server Transaction Log File

The best way of finding transaction log file size and usage is “try to shrink” method in my opinion. I always use this method.

Right click on the database and click Properties. Then click Tasks->Shrink->Files

Choose Transasction Log File from File Type Section in the below screen. Then you can see the location, size and usage of the Transaction Log File from this screen.

Location: Transaction Log File Path

Currently allocated space: Transaction Log File Size

Available free space: You can see how many percent of transaction log file is used. If available free space is near to %100, you can shrink transaction log file. To shrink the transaction log file you can read my article “How To Shrink SQL Server Transaction Log”

Importance of Transaction Log File

For example, your SQL Server server has encountered an unexpected error and has either been shut down or restarted.

In such a case, SQL Server uses the transaction log file to bring the database into a consistent structure.

SQL Server Engine rolls forward completed transactions with the information it receives from the Transaction Log file, and rolls back incomplete transactions.

This ensures database consistency. I would recommend reading the article called “What is Database Checkpoint” to understand this process in more detail.

If there is a problem in the database, we can use the transaction log file backups to return the database to the most recent time.

I would recommend reading the “What is Database Recovery Model” article.

The SQL Server Transaction Log file consists of one or more virtual log files.

These virtual log files can seriously affect performance.

You can find the details of the article named “Vlf (Virtual Log File) Count“.