RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


SQL Server Sanity: Maintain Healthy Transaction Logs for Easy Data Recovery

Learn how executing frequent backups and maintaining healthy transaction logs in SQL Server leads to quick recovery. Failing to do so can mean having to re-enter a lot of data.

ata is your business, and maintaining a healthy backup and recovery plan is vital to protecting your data. SQL Server's transaction logs, which bridge the most recent changes to the last backup, should be a part of that backup and recovery plan. Without a healthy, well-maintained transaction log, you can recover old data but your users will still have to re-enter all of their changes since the last backup. Fail to maintain transaction logs and you may fail to keep your job. Fortunately, SQL Server's transaction logs are easy to maintain.

How SQL Server Transaction Logs Work
Most client/server databases offer a transaction log, which is simply a separate file where the server tracks operations in the following sequence:

  1. The log notes that the server expects a change.
  2. The log notes that the server made a change.
  3. The log notes that the server committed the change to the data file.

When users change data, SQL Server doesn't write that change directly to the data. Rather, SQL Server locates the appropriate data and then loads it into a special area of RAM called the data cache. Changes are made in RAM. Then, SQL Server copies changes waiting in RAM to the transaction log. Only then does SQL Server write changes to the actual data file. This is called a write-ahead log because SQL Server writes changes to the log before it writes changes to the actual data file. This approach is quite a bit faster than writing directly to the data file.

Perhaps more important than performance is the transaction log's role in data recovery. Thanks to the transaction log, you can recover changes right up to the error from which you're recovering. During the recovery process, SQL Server scans the log for changes that weren't committed. That way, the database can finish what it started.

The log stores changes in three parts:

  • Backed-up: This section contains changes that were committed the last time you backed up the database.
  • Inactive: This section contains committed changes that haven't been backed-up yet.
  • Active: This section contains committed and uncommitted changes (depending on their sequence and relation to other changes).

SQL Server identifies each event with a log sequence number (LSN) as follows:

101 Begin transaction 1
102 Update transaction 1
103 Begin transaction 2
104 Update transaction 2
105 Commit transaction 1
106 Checkpoint
107 Commit transaction 2

When SQL Server begins a backup, it records the current LSN. For instance, from the checkpoint at LSN 106, SQL Server searches for the oldest open transaction. In this case, that's 103 because that transaction is uncommitted at the checkpoint. Therefore, transactions 103 and higher represent transactions that occurred during the actual backup process. When the backup is complete, SQL Server backs up the transactions from 103 to the most current transaction.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date