Database Reference
In-Depth Information
Another important factor is the number of databases. When you place transaction logs from multiple
databases to a single disk array, log I/O access becomes random rather than sequential. You should factor in such
behavior when testing your I/O subsystem and choose the test scenarios that represent the workload that you
expect to have in production.
Most important, you should store the transaction log to highly redundant disk array. It is impossible to recover
the database in a transactionally consistent state if the transaction log has been corrupted.
Summary
SQL Server uses a transaction log to store information about all data modifications made to the database. It allows
SQL Server to keep the database transactionally consistent, even in the event of unexpected shutdown or crash.
SQL Server uses a write-ahead logging mechanism, which guarantees that log records are always saved into the
log file before updated data pages are saved to the data files. SQL Server uses a small buffer to cache log records in
memory, saving all of them at once when needed.
The transaction log is a wraparound file, which internally consists of multiple virtual log files. Every virtual log
file can either be active or inactive. Transaction log truncation marks some VLFs as inactive, making them ready for
reuse. In the SIMPLE recovery model, SQL Server truncates the transaction log at CHECKPOINT. In the FULL and
BULK-LOGGED recovery models, SQL Server truncates the transaction log during log backups.
There are a number of issues that can prevent transaction log truncation. The most common ones are lack of
transaction log backups in the FULL and BULK-LOGGED recovery models, or long-running uncommitted transactions.
Moreover, some SQL Server processes, such as replication, database mirroring, and a few others, can prevent log
truncation if some part of the active log is unprocessed. You can examine what prevents log truncation by analyzing the
log_reuse_wait_desc
column in
sys.databases
view.
You should avoid situations where the transaction log has too many or too few virtual log files. Either
circumstance negatively affects system performance. For databases that require large transaction files, you can pre-
allocate the transaction log with 8000MB chunks, which makes 16 VLF of about 500MB each.
It is recommended that you manage the transaction log size manually to avoid log auto-growth. However, you
should still keep auto-growth enabled to avoid a
“9002: Transaction Log Full”
error. Auto-growth size should be
specified in MB rather than as a percent. You need to fine-tune the size based on the I/O performance of the system.
Large auto-growth chunks reduce the number of VLFs created, however SQL Server zero-initializes the newly
allocated space, suspending all sessions that generate log records during that time.
Fast transaction log throughput is essential for good performance, especially with OLTP systems. You must store
the transaction log on a fast disk array, minimizing writing latency. Most important, that array must be redundant.
It is impossible to recover the database in a transactionally consistent state if the transaction log is corrupted.
