SQL Server Transaction Log Basics

Here at RDX, our internal and external clients can have varying levels of SQL Server knowledge. Explaining technical details to clients is, in my opinion, one of the most rewarding tasks we perform. Not only does this allow us to provide client-specific recommendations, it allows our relationships to evolve from ‘support personnel’ to a trusted team member. In my second blog post, I am addressing one of the topics that come up often – SQL Server transaction logs.

Transaction Logs and Their Internal Structure

Each database within SQL Server has at least one transaction log file. The default file extension for these is “LDF”. The transaction log files are internally split into virtual log files (VLFs). VLFs are created when a transaction log file is created and when the transaction log file grows. With improper autogrowth settings or within large transaction log files, the number of VLFs can grow to the point that performance degrades. You can identify the number of VLFs in a database by running this command and noting the number of rows returned:

DBCC LogInfo

Recovery Models and Their Effects

Some data is transient or can easily be rolled forward through your application. For these databases, the Simple recovery model can be used. Full and differential backups are the available backup types. Records in the transaction log file are cleared once the transaction completes. In other situations, you need to minimize data loss, also referred to as ‘point-in-time’ recovery. Using the Full recovery model in conjunction with full, transaction log, and, at times, differential backups allows you to meet this need. Records in the transaction log remain in the file until a transaction log backup is taken. Without transaction log backups, databases using the Full recovery model will experience exponential transaction log file growth. During large bulk operations, the bulk-logged recovery model can be used. This minimizes the amount of logging taking place in the transaction log and, therefore, your disk space requirements. Point-in-time recovery is not supported using this recovery model. If point-in-time recovery is required, transaction log backups should be taken immediately before changing the database to bulk-logged recovery and after the database is changed to use Full recovery.

How Transactions are Tracked

Within the VLFs are records of all transactions that occur within its database, marked with log sequence numbers (LSNs). These LSNs are important for recovering your data to a point in time if a disaster or failure occurs. All backups begin with creating a header containing the LSN details. This information can be used to ensure you have all of the backups you need to recover. Restoring backups out of order, skipping a restore of a transaction log backup, and restoring only transaction log backups are not supported. If a recent transaction log backup is deleted, it will affect your ability to recover, and a new full backup should be taken as soon as possible. If you suspect a transaction log backup has been deleted, you can compare the header details below.

  • FirstLSN
  •       -LSN at the beginning of the backup

  • LastLSN
  •       -LSN at the end of the backup
          -With transaction log backups, LastLSN can be compared to the FirstLSN int he suspected next transaction log backup to confirm continuity.

  • DatabaseBackupLSN
  •       -FirstLSN of the last full database backup

  • DifferentialBaseLSN
  •       -FirstLSN of the last full database backup

    Transaction logs are a critical concept to master when working with SQL Server. Without proper attention they can impact the performance of your databases or affect your ability to recover from a disaster situation. Discussions between DBAs (‘accidental’ or otherwise) and application or business decision makers need to occur periodically to ensure that minimal data is lost outside of the SLA.

    I hope this post has provided you with useful insight into your SQL Server transaction logs. In the coming weeks, Matt Nelson will be explaining how to manage the number of VLFs in your databases. Stay tuned!

    DBA Tips