SQL Server How To: Transaction Log Maintenance for Painless Data Recovery

Data 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:

• The log notes that the server expects a change.
• The log notes that the server made a change.
• 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:

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.