Microsoft SQL Server Best Practices: Transaction Log Basics

Microsoft’s SQL Server has been out for almost three decades. Version 1.0 on OS/2 was released in 1989; the most recent version is SQL Server 2016. The first version of SQL Server I ever worked with was version 6.5 back in 1997. Here I will cover one aspect of SQL Server at a time and offer my thoughts on the best practices to consider while configuring SQL Server. The intent is not to look at every single SQL Server setting, but rather at its overall architecture.

SQL Server Transaction Log Basics

Transaction log files are an important part of any database on a SQL Server. A transaction log does not hold data permanently; rather, database files do. However, transaction logs play a very important role in the operation of a database and during the recovery process in the event of a catastrophic failure. I will explain the basics of a transaction log and how to configure them for optimal performance.

What is a Transaction Log File?

While configuring a SQL Server, administrators focus on various aspects of the system like high availability, CPU speed, and memory to make sure the server is configured for optimal performance. However, I have seen that database administrators (DBAs) tend to ignore the configuration of transaction logs, which is a key component of every database.

A transaction log is a file that is an integral part of every SQL Server database. Every database on a SQL Server comprises of at least one database file and one transaction log file. The transaction log file is a like a gateway to the database. All transactions are written sequentially to the transaction log file before they are committed to the database. In other words, a database cannot exist without a transaction log file.

Functionality Supported by a Transaction Log File

  • Recovery of an individual transaction
  • Recovery of all incomplete transactions when SQL Server is started
  • Point-in-Time Recovery
  • Transaction Replication
  • Standby-Server Solution (Always On, Database Mirroring, Log Shipping etc.)

Internal Structure of Transaction Log Files

The transaction log can be made up of one or more physical files. Each physical file is divided into multiple virtual log files. These virtual log files do not have a fixed size, nor can you specify the number of virtual log files that will make up a physical file. The database engine chooses the size of the virtual log files dynamically while it is creating or extending log files. All transaction are stored sequentially in a log file. The database engine automatically tries to maintain a small number of virtual files.

SQL Server Best Practices

Image courtesy of Microsoft

The transaction log is a wrap-around file. Consider a database with one physical log file divided into four virtual log files. When the database is created, the logical log file begins at the start of the physical log file. New log records are added at the end of the logical log file and expand toward the end of the physical log. Log truncation frees any virtual logs whose records all appear in front of the minimum recovery log sequence number (MinLSN). The MinLSN is the log sequence number of the oldest log record that is required for a successful database-wide rollback. The transaction log in the example database would look similar to the one in the following illustration:

SQL Server Best Practices

As I mentioned above, the transaction log is a gateway to the database. All operations that modify data are logged to the transaction log, so log files tend to grow exponentially. This is a very common problem that DBAs deal with on a daily basis. Here are other common operations that cause the log file to grow:

  • Uncommitted transactions
  • Index Operations (CREATE INDEX, rebuilding indexes, etc.)
  • Transactions that haven’t replicated
  • Long running transactions
  • Large transactions

With the basics illustrated in this blog post, we now have a solid understanding of the makeup and functionality of a transaction log. In my next blog post, I will discuss about some of the key aspects to consider while configuring the transaction log for a database.

READ PART TWO

There’s more to explore at Smartbridge.com!

Sign up to be notified when we publish articles, news, videos and more!