SQL Server 7.0 Transaction Log

The transaction log ensures atomicity and durability

SQL Server is a transaction-based relational database system, and as such, needs some way to guarantee transaction atomicity and durability. Atomicity means that transactions function as single units of work: All statements in a transaction either run to completion, or none of the statements start. Durability means that after a transaction completes (or commits), the database system guarantees that the changes are permanent.

SQL Server uses a transaction log to ensure that transactions are atomic and durable. SQL Server writes every database change to the transaction log, so that if a transaction starts but doesn't complete, you can retrieve the changes from the log and roll them back or undo them. This capability to roll back changes means the transactions are atomic. When a transaction commits, SQL Server immediately writes all the log records pertaining to that transaction to permanent storage on disk. In that way, even if the system fails before SQL Server writes the changed data pages to disk, the log records are on disk. When SQL Server starts again, the log gives it enough information to recover, or roll forward, any transactions that completed but whose corresponding data pages weren't written to disk. This capability to recover transactions means SQL Server transactions are durable. Another benefit of the transaction log is that you can make incremental database backups. By backing up the transaction log but not the entire database, you keep a record of changes since the last backup. A sequence of log backups is a record of changes to a database after the last full database backup.

Every SQL Server database contains separate log files for the transaction log, and each database can have one or more log files. The log contains a record for every activity that changed the database, including each INSERT, DELETE, and UPDATE. The log also tracks creation of new objects or new users and permission changes. By backing up the transaction log but not the entire database, you keep a record of changes since the last backup. A sequence of log backups is a record of changes to a database after the last full database backup.

New Structure
Previous versions of SQL Server stored the transaction log as a system table called syslogs. The log and data could compete for the same total space within a database. Log pages took up space in the memory cache, just as pages from user tables did. You could run commands against the transaction log as you could against any other table.

But in SQL Server 7.0, the log isn't a table. SQL Server 7.0 stores the transaction log on one or more physical files that you specify when you create the database or by altering the database.

Figure 1, page 24, represents a physical log file. The file is divided into at least two virtual log files so that SQL Server can easily manage the log internally. SQL Server avoids having many small virtual log files. The number of virtual log files depends on how fast the log grows. If a log file grows in small increments, it will tend to have many small virtual log files. If the log file grows in larger increments, SQL Server will create fewer virtual log files of larger size.

SQL Server marks each log record with a log sequence number (LSN). The LSN for each log record is a unique, increasing value that can mark a position in the log for recovery. The earliest record in the log that is part of an open, or still active, transaction is the minimum LSN. All log records before that point are part of the inactive log.

Because SQL Server 7.0 doesn't store the transaction log as a database table, the maximum row size increases and log activity doesn't compete with data in SQL Server's memory cache. SQL Server 6.5 and earlier versions limit maximum row size to 1962 bytes for any single row. One row can never span multiple pages, and each page is 2KB in size, or 2048 bytes. The page overhead is 32 bytes, and page data can consume 2016 bytes. The reason SQL Server 6.5 limits maximum row size to 1962 bytes is because of the way the transaction log stores information. Every insert, every delete, and most update operations make a copy of each row that the transaction affects, and record other information with the row's data. SQL Server 6.5 adds about 50 bytes of logging information as overhead, so if you try to insert a 2000-byte row, the row and the log information won't fit in the transaction log table. SQL Server 6.5 limits single rows to 50 bytes smaller than the space available for page data.

SQL Server 7.0 allows a longer row size. Pages are 8KB, or 8192 bytes, with a 96-byte header. This structure allows 8096 bytes for data, which is the maximum size of a single row in SQL Server 7.0. Log records in SQL Server 7.0 can be up to 32KB in size.

Because SQL Server 7.0 doesn't store the transaction log in a table, log activity doesn't compete with data in SQL Server's memory cache. Each database log has a cache of buffer pages that SQL Server uses to read and write log pages. SQL Server manages the log caches separately from the data buffer cache to reduce synchronization between log and data buffers. SQL Server adjusts log cache size dynamically to optimize performance.

Changes in Log Size

One of SQL Server 7.0's best features is that you can set database files to automatically grow. When you create the database, if you specify an upper limit for the log file size that is greater than the initial size, or if you specify no upper limit, the physical log file will expand as needed. You use the growth increment value to specify how much the log file can expand.

Log files run out of room when the end of the log reaches the end of the physical file and when SQL Server has not yet backed up the log records before the minimum LSN. The log records before the minimum LSN play no role in recovery, but SQL Server needs them to roll forward updates when it uses log backups to restore a database from backups.

SQL Server needs the log records before the minimum LSN only to maintain a sequence of log backups. If you don't maintain a log backup sequence, SQL Server will overwrite the log records before the minimum LSN. SQL Server determines that you aren't maintaining a sequence of log backups if any one of the following is true:

In any of these situations, when SQL Server reaches the end of the physical log file, it reuses space in the physical file by returning to the beginning of the file. In effect, SQL Server 7.0 recycles space in the log file that isn't needed for recovery or backup. If you maintain a log backup sequence, SQL Server can't overwrite the portion of the log before the minimum LSN until you've backed up those log records.

Truncating the log affects the records within the log file. Truncating the log marks the space used by the truncated records as available for reuse, but it doesn't change the physical file's size. You can force the physical log file to shrink in one of three ways. You can run the DBCC SHRINKFILE command on the log files; you can run DBCC SHRINKDATABASE, which shrinks every file in the database; or you can set to TRUE the autoshrink database option.

When you use DBCC SHRINKFILE, you specify a size in megabytes that you want the file to shrink to. When you use DBCC SHRINKDATABASE, you specify a target percentage, which specifies the amount of free space you want to leave in each file of the database. For example, if a 100MB file uses only 50MB and you specify a 20 percent target, the file will shrink to 60MB (50MB plus 20 percent, or 10MB) and SQL Server will return 40MB of free space to the operating system.

Because log files shrink by a deferred shrink operation, you probably won't immediately see the decrease in file size. In addition, SQL Server can only shrink complete virtual log files and can physically shrink only from the end. If the active log is at the end of the physical file, no shrinking can take place. You'll need to truncate the log so that virtual log files earlier in the file are reusable, and then issue some commands that create log records, so that the active log will circle back to the beginning of the physical file. Then the physical file can be shrunk.

The autoshrink database option lets a database shrink automatically. This option has the same effect as using DBCC SRHINKDATABASE with a 25 percent target. SQL Server has a background thread that performs the automatic shrinking. The autoshrink process kicks in every 30 minutes and determines the size to shrink the log. The autoshrink function then marks the log to shrink to that size whenever it can, which is when the log is truncated or when it's backed up. If the log is never truncated or backed up, the physical file will not shrink.

The changes in SQL Server 7.0 leave little for a DBA to do to manage the transaction log. You might want to set up alerts to notify you if the physical file expands so that you can determine how often you need to back up the log. To determine how much log space the log files use, you can run the following command:

dbcc sqlperf(logspace)

This command will give you the total file size, and the percentage full, for each of the database's logs.

As a transaction-based database system, SQL Server records every database change in the transaction log. SQL Server 7.0 handles transaction log space in a way that requires much less intervention by DBAs than SQL Server 6.5 required.