Редактиране

Споделяне чрез

The transaction log

  • Статия

Applies to: SQL Server

Every SQL Server database has a transaction log that records all transactions and the database modifications made by each transaction.

The transaction log is a critical component of the database. If there's a system failure, you need that log to bring your database back to a consistent state.

Warning

Never delete or move this log unless you fully understand the ramifications of doing so.

For information about the transaction log architecture and internals, see the SQL Server transaction log architecture and management guide.

Tip

Known good points from which to begin applying transaction logs during database recovery are created by checkpoints. For more information, see Database checkpoints (SQL Server).

Operations supported by the transaction log

The transaction log supports the following operations:

  • Individual transaction recovery.
  • Recovery of all incomplete transactions when SQL Server is started.
  • Rolling a restored database, file, filegroup, or page forward to the point of failure.
  • Supporting transactional replication.
  • Supporting high availability and disaster recovery solutions: Always On availability groups, database mirroring, and log shipping.

Individual transaction recovery

If an application issues a ROLLBACK statement, or if the Database Engine detects an error such as the loss of communication with a client, the log records are used to roll back the modifications made by an incomplete transaction.

Recovery of all incomplete transactions when SQL Server is started

If a server fails, the databases might be left in a state where some modifications were never written from the buffer cache to the data files, and there might be some modifications from incomplete transactions in the data files. When an instance of SQL Server is started, it runs a recovery of each database. Every modification recorded in the log that might not have been written to the data files is rolled forward. Every incomplete transaction found in the transaction log is then rolled back to make sure the integrity of the database is preserved. For more information, see Restore and Recovery Overview (SQL Server).

Rolling a restored database, file, filegroup, or page forward to the point of failure

After a hardware loss or disk failure affecting the database files, you can restore the database to the point of failure. You first restore the last full database backup and the last differential database backup, and then restore the subsequent sequence of the transaction log backups to the point of failure.

As you restore each log backup, the Database Engine reapplies all the modifications recorded in the log to roll forward all the transactions. When the last log backup is restored, the Database Engine then uses the log information to roll back all transactions that weren't complete at that point. For more information, see Restore and Recovery Overview (SQL Server).

Support transactional replication

The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database. For more information, see How Transactional Replication Works.

Support high availability and disaster recovery solutions

The standby-server solutions, Always On availability groups, database mirroring, and log shipping, rely heavily on the transaction log.

In an Always On availability groups scenario, every update to a database on the primary replica is immediately reproduced in the separate copies of the database on all the secondary replicas. The primary replica sends each log record immediately to the secondary replicas, which apply the incoming log records to the availability databases, continually rolling forward the log. For more information, see Always On failover cluster instances (SQL Server).

In a log shipping scenario, the primary server sends the transaction log backups of the primary database to one or more destinations. Each secondary server restores the log backups to its local secondary database. For more information, see About log shipping (SQL Server).

In a database mirroring scenario, every update to a database, the principal database, is immediately reproduced in a separate, full copy of the database, the mirror database. The principal server instance sends each log record immediately to the mirror server instance, which applies the incoming log records to the mirror database, continually rolling it forward. For more information, see Database Mirroring (SQL Server).

Transaction log characteristics

Characteristics of the SQL Server Database Engine transaction log:

  • The transaction log is implemented as a separate file or set of files in the database. The log cache is managed separately from the buffer cache for data pages, which results in simple, fast, and robust code within the SQL Server Database Engine. For more information, see Transaction Log Physical Architecture.

  • The format of log records and pages isn't constrained to follow the format of data pages.

  • The transaction log can be implemented in several files. The files can be defined to expand automatically by setting the FILEGROWTH value for the log. This reduces the potential of running out of space in the transaction log, while at the same time reducing administrative overhead. For more information, see ALTER DATABASE (Transact-SQL) File and Filegroup Options.

  • The mechanism to reuse the space within the log files is quick and has minimal effect on transaction throughput.

For information about the transaction log architecture and internals, see the SQL Server transaction log architecture and management guide.

Transaction log truncation

Log truncation frees space in the log file for reuse by the transaction log. You must regularly truncate your transaction log to keep it from filling the allotted space. Several factors can delay log truncation, so monitoring log size matters. Some operations can be minimally logged to reduce their impact on transaction log size.

Log truncation deletes inactive virtual log files (VLFs) from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the Physical transaction log. If a transaction log is never truncated, it eventually fills all the disk space allocated to physical log files.

To avoid running out of space, unless log truncation is delayed for some reason, truncation occurs automatically after the following events:

  • Under the simple recovery model, after a checkpoint.

  • Under the full recovery model or bulk-logged recovery model, if a checkpoint has occurred since the previous backup, truncation occurs after a log backup (unless it's a copy-only log backup).

  • When you first create a database using the full recovery model, the transaction log is reused as needed (similar to a database using the simple recovery model), up until the time you create a full database backup.

For more information, see Factors that can delay log truncation, later in this article.

Log truncation doesn't reduce the size of the physical log file. To reduce the physical size of a physical log file, you must shrink the log file. For information about shrinking the size of the physical log file, see Manage the size of the transaction log file. However, keep in mind Factors that can delay log truncation. If the storage space is required again after a log shrink, the transaction log will grow again and by doing that, introduce performance overhead during log grow operations.

Factors that can delay log truncation

When log records remain active for a long time, transaction log truncation is delayed, and the transaction log can fill up, as we mentioned earlier in this article.

Important

For information about how to respond to a full transaction log, see Troubleshoot a full transaction log (SQL Server Error 9002).

Really, log truncation can be delayed by various reasons. Learn what, if anything, is preventing your log truncation by querying the log_reuse_wait and log_reuse_wait_desc columns of the sys.databases catalog view. The following table describes the values of these columns.

log_reuse_wait value log_reuse_wait_desc value Description
0 NOTHING Currently there are one or more reusable virtual log files (VLFs).
1 CHECKPOINT No checkpoint has occurred since the last log truncation, or the head of the log hasn't yet moved beyond a virtual log file (VLF) (All recovery models).

This is a routine reason for delaying log truncation. For more information, see Database checkpoints (SQL Server).
2 LOG_BACKUP A log backup is required before the transaction log can be truncated. (Full or bulk-logged recovery models only)

When the next log backup is completed, some log space might become reusable.
3 ACTIVE_BACKUP_OR_RESTORE A data backup or a restore is in progress (All recovery models).

If a data backup is preventing log truncation, canceling the backup operation might help the immediate problem.
4 ACTIVE_TRANSACTION A transaction is active (All recovery models):

A long-running transaction might exist at the start of the log backup. In this case, freeing the space might require another log backup. Long-running transactions prevent log truncation under all recovery models, including the simple recovery model, under which the transaction log is generally truncated on each automatic checkpoint.

A transaction is deferred. A deferred transaction is effectively an active transaction whose rollback is blocked because of some unavailable resource. For information about the causes of deferred transactions and how to move them out of the deferred state, see Deferred Transactions (SQL Server).

Long-running transactions might also fill up tempdb's transaction log. tempdb is used implicitly by user transactions for internal objects such as work tables for sorting, work files for hashing, cursor work tables, and row versioning. Even if the user transaction includes only reading data (SELECT queries), internal objects might be created and used under user transactions. Then the tempdb transaction log can be filled.
5 DATABASE_MIRRORING Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database. (Full recovery model only).

For more information, see Database Mirroring (SQL Server).
6 REPLICATION During transactional replications, transactions relevant to the publications are still undelivered to the distribution database. (Full recovery model only)

For information about transactional replication, see SQL Server Replication.
7 DATABASE_SNAPSHOT_CREATION A database snapshot is being created (All recovery models).

This is a routine, and typically brief, cause of delayed log truncation.
8 LOG_SCAN A log scan is occurring (All recovery models).

This is a routine, and typically brief, cause of delayed log truncation.
9 AVAILABILITY_REPLICA A secondary replica of an availability group is applying transaction log records of this database to a corresponding secondary database. (Full recovery model only).

For more information, see What is an Always On availability group?.
10 - For internal use only
11 - For internal use only
12 - For internal use only
13 OLDEST_PAGE If a database is configured to use indirect checkpoints, the oldest page on the database might be older than the checkpoint log sequence number (LSN). In this case, the oldest page can delay log truncation (All recovery models).

For information about indirect checkpoints, see Database checkpoints (SQL Server).
14 OTHER_TRANSIENT This value is currently not used.
16 XTP_CHECKPOINT An In-Memory OLTP checkpoint needs to be performed. For memory-optimized tables, an automatic checkpoint is taken when transaction log file becomes bigger than 1.5 GB since the last checkpoint (includes both disk-based and memory-optimized tables).

For more information, see Checkpoint Operation for Memory-Optimized Tables and [Logging and Checkpoint process for In-Memory Optimized Tables] (https://blogs.msdn.microsoft.com/sqlcat/2016/05/20/logging-and-checkpoint-process-for-memory-optimized-tables-2/)

Operations that can be minimally logged

Minimal logging involves logging only the information that is required to recover the transaction without supporting point-in-time recovery. This article identifies the operations that are minimally logged under the bulk-logged recovery model (as well as under the simple recovery model, except when a backup is running).

Minimal logging isn't supported for memory-optimized tables.

Under the full recovery model, all bulk operations are fully logged. However, you can minimize logging for a set of bulk operations by switching the database to the bulk-logged recovery model temporarily for bulk operations. Minimal logging is more efficient than full logging, and it reduces the possibility of a large-scale bulk operation filling the available transaction log space during a bulk transaction. However, if the database is damaged or lost when minimal logging is in effect, you can't recover the database to the point of failure.

The following operations, which are fully logged under the full recovery model, are minimally logged under the simple and bulk-logged recovery model:

  • Bulk import operations (bcp, BULK INSERT, and INSERT). For more information about when bulk import into a table is minimally logged, see Prerequisites for minimal logging in bulk import.

    When transactional replication is enabled, BULK INSERT operations are fully logged even under the bulk logged recovery model.

  • SELECT - INTO clause operations.

    When transactional replication is enabled, SELECT INTO operations are fully logged even under the bulk logged recovery model.

  • Partial updates to large value data types, using the .WRITE clause in the UPDATE statement when inserting or appending new data. Minimal logging isn't used when existing values are updated. For more information about large value data types, see Data types.

  • WRITETEXT and UPDATETEXT statements when inserting or appending new data into the text, ntext, and image data type columns. Minimal logging isn't used when existing values are updated.

    Warning

    The WRITETEXT and UPDATETEXT statements are deprecated; avoid using them in new applications.

  • If the database is set to the simple or bulk-logged recovery model, some index DDL operations are minimally logged whether the operation is executed offline or online. The minimally logged index operations are as follows:

    • CREATE INDEX operations (including indexed views).

    • ALTER INDEX REBUILD or DBCC DBREINDEX operation.

      Index build operations use minimal logging, but might be delayed when there's a concurrently executing backup. This delay is caused by the synchronization requirements of minimally logged buffer pool pages when using the simple or bulk-logged recovery model.

      Warning

      The DBCC DBREINDEX statement is deprecated; avoid using it in new applications.

    • DROP INDEX new heap rebuild (if applicable). Index page deallocation during a DROP INDEX operation is always fully logged.

Task Article
Manage the transaction log - Manage the size of the transaction log file

- Troubleshoot a full transaction log (SQL Server Error 9002)
Back up the transaction log (full recovery model only) - Back up a transaction log

- Back Up the Transaction Log When the Database Is Damaged (SQL Server)
Restore the transaction log (full recovery model only) - Restore a Transaction Log Backup (SQL Server)

Related content