SQL Server Transaction transaction log

transactions (SQL Server)

First, the concept of business
A transaction is a mechanism, a sequence of operations, that contains a set of database operations commands that either execute all or do not. Therefore, a transaction is an inseparable unit of work logic. The transaction is used as the smallest control unit when concurrent operations are performed on the database system. This is especially applicable to multi-user simultaneous operation of the data communication system. For example: booking, banking, insurance and securities trading system.

Second, transaction properties
Transaction 4 Large Properties:
1 atomicity (atomicity): A transaction is a complete operation.
2 Consistency (consistency): When a transaction completes, the data must be in a consistent state.
3 Isolation (Isolation): All concurrent transactions that modify data are isolated from each other.
4 Persistence (Durability): After a transaction completes, its effect on the system is permanent.

Third, create a transaction
Statements that manage transactions in T-sql:
1 starting transaction: BEGIN TRANSACTION
2 COMMIT TRANSACTION: COMMIT Transaction
3 Rolling back transactions: ROLLBACK TRANSACTION

Transaction classification:
1 Explicit transactions: explicitly specify the beginning of a transaction with BEGIN transaction.
2 Implicit transaction: Open implicit transaction: Set implicit_transactions on, when operating in implicit transaction mode, SQL Servler automatically starts a new transaction after committing or rolling back the transaction. Cannot describe the beginning of a transaction, only the transaction must be committed or rolled back.
3 autocommit transaction: The default mode of SQL Server, which treats each individual T-SQL statement as a transaction. If executed successfully, it is automatically committed or rolled back.

transaction log (SQL Server)Tue Oct 03 2017

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

The transaction log is a critical component of the database. if the system fails, you will need to rely on the log to restore the database to a consistent state.

Important

Never delete or move this log unless you fully understand the consequences of performing this operation.

Tips

Checkpoints create some normal points, and the transaction log is applied from these normal points during database recovery. For more information, see Database Checkpoints (SQL Server).

operations supported by the transaction log

The transaction log supports the following operations:

• Recovery of individual transactions.

• resumes all outstanding transactions at SQL Server startup.

• Rolls the restored database, file, filegroup, or page forward to the point of failure.

• transactional replication is supported.

• Support for high availability and disaster recovery solutions: AlwaysOn availability Groups, database mirroring, and log shipping.

Recovery of individual transactions

If the application issues a ROLLBACK statement, or if the database engine detects an error (such as losing communication with the client), use logging to rollback the modifications made by the unfinished transaction.

recover all outstanding transactions at SQL Server startup

In the event of a server failure, the database may be in a state where some modifications have not been written to the data file from the cache, and there are modifications made in the data file that are not completed by the firm. When you start an instance of SQL Server, it performs a recovery operation on each database. each modification recorded in the roll forward log that may not have been written to the data file. each outstanding transaction that is found in the transaction log is rolled back to ensure the integrity of the database.

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

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

When each log backup is restored, the database engine will reapply all the changes recorded in the log and roll forward all transactions. after the last log backup is restored, the database engine uses the log information to fall back to all transactions that were not completed at that point.

Support Transactional Replication

The Log Reader Agent monitors the transaction log for each database that has been configured for transactional replication and copies the transactions that have been marked for replication from the transaction log to the distribution database. For more information, see how Transactional replication works.

support for high availability and disaster recovery solutions

The standby server solution, AlwaysOn availability groups, database mirroring, and log shipping are heavily dependent on the transaction log.

In an AlwaysOn availability group scenario, each update (primary copy) of the database is reproduced directly in a complete and independent copy of the database (secondary copy). The primary replica sends each log record directly to the secondary replica, which applies incoming log records to the availability group database and keeps rolling forward. For more information, see AlwaysOn Failover cluster instances

In a log shipping scenario, the primary server sends the active transaction log for the primary database to one or more target servers. Each secondary server restores the log to its local secondary database. For more information, see About log shipping.

In a database mirroring scenario, each update of the database (principal database) is regenerated immediately in a separate, complete copy of the database (mirror database). The principal server instance immediately sends each log record to the mirror server instance, and the mirror server instance applies the incoming log records to the mirrored database, thus continuing to roll it forward. For more information, see Database Mirroring.

Transaction Log Characteristics

Characteristics of the SQL Server database engine transaction log:

• The transaction log is implemented as a separate file or a set of files in the database. The log cache is managed separately from the buffer cache for data pages, so you can generate simple, fast, and powerful code in the database engine. For more information, see Transaction Log Physical architecture.
• The format of the log records and pages does not have to conform to the format of the data page.
• The transaction log can be implemented on several files. These files can be defined as auto-scaling by setting the FILEGROWTH value of the log. This reduces the likelihood of insufficient space within the transaction log, while reducing administrative overhead. For more information, see ALTER DATABASE (Transact-SQL).
• The mechanism for reusing space in a log file is faster and has minimal impact on transactional throughput.

transaction log Truncation

Log truncation frees the log file space for reuse by the transaction log. The transaction log must be truncated periodically to prevent it from filling up the allocated space (absolutely!). ）。 Several factors may delay log truncation, so it is important to monitor the log size. Some operations can be logged with minimal log volume to reduce their impact on the transaction log size.

Log truncation removes inactive virtual log files from the logical transaction log of the SQL Server database, freeing up space in the logical log for the physical transaction log to reuse the space. If the transaction log is never truncated, it will eventually fill up all the disk space allocated to the physical log file.

To avoid a lack of space, the truncation occurs automatically after the following event, unless the log truncation is delayed for some reason:

• In the simple recovery mode, occurs after the checkpoint.

• Under the full recovery model or bulk-logged recovery model, if a checkpoint has been generated since the last backup, it is truncated after the log backup (unless it is a copy-only log backup).

  For more information, see Factors that may delay log truncation later in this topic.

Note

Log truncation does not reduce the size of the physical log file. to reduce the physical size of the physical log files, you must shrink the log files. For information about shrinking the size of the physical log file, see Manage the size of the Transaction log files.

factors that can delay log truncation

When logging is active for a long time, transaction log truncation is deferred and the transaction log may fill up, as we mentioned earlier in this topic (very long).

[! IMPORTANT} For information about how to respond to a full transaction log, see Troubleshooting a Full transaction log (SQL Server error 9002).

In fact, log truncation can be delayed for a number of reasons. Query the log_reuse_wait and Log_reuse_wait_desc columns of the sys.databases catalog view to see what factors, if any, prevent log truncation. The values for these columns are described in the following table.

log_reuse_wait Value	Log_reuse_wait_desc Value	Description
0	Nothing	There is currently one or more reusable virtual log files.
1	CHECKPOINT	Checkpoints have not been generated since the last log truncation, or the log header has not been moved across a virtual log file. (All Recovery models) This is a common cause of log truncation delays. For more information, see Database Checkpoints (SQL Server).
2	Log_backup	A log backup is required before the transaction log is truncated. (Full recovery model or bulk-logged recovery model only) After the next log backup is complete, some log space may become reusable.
3	Active_backup_or_restore	Data backup or restore is in progress (all recovery models). If a data backup prevents log truncation, canceling the backup operation may help resolve this issue directly caused by the backup.
4	active_transaction	transaction is active (All recovery models): A long-running transaction may exist at the beginning of a log backup.   note that long-running transactions will block log truncation in all recovery modes, including the simple recovery model, in which the transaction log is typically truncated at each automatic checkpoint. delay transaction.   "deferred transaction" is a valid active transaction, because some resources are not available and their rollback is blocked.   tempdb is used implicitly by user transactions for internal objects such as worksheets for sorting, working files for hashing, cursor worksheets, and row versioning.   SELECT   query), internal objects may be created and used on behalf of the user transaction,  < Span data-ttu-id= "7364f-206" and the tempdb transaction log is then populated.
5	Database_mirroring	Database mirroring is paused, or in high-performance mode, the mirrored database lags significantly behind the principal database. (Full recovery model only) For more information, see Database Mirroring (SQL Server).
6	REPLICATION	During transactional replication, the transaction related to the publication is still not delivered to the distribution database. (Full recovery model only) For information about transactional replication, see SQL Server Replication.
7	Database_snapshot_creation	The database snapshot is being created. (All Recovery models) This is a common cause of log truncation delays and is often the main cause.
8	Log_scan	A log scan occurs. (All Recovery models) This is a common cause of log truncation delays and is often the main cause.
9	Availability_replica	A secondary replica of the availability group is applying transaction log records for this database to the appropriate secondary database. (Full Recovery model) For more information, see: AlwaysOn Availability Groups Overview (SQL Server).
10	—	For internal Use only
11	—	For internal Use only
12	—	For internal Use only
13	Oldest_page	If the database is configured to use an indirect checkpoint, the oldest page in the database may be earlier than the checkpoint LSN. in this scenario, 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 not currently used.

operation to minimize log volume

Minimal logging refers to the information that is required to recover a transaction only if it does not support point-in-time recovery. This topic describes actions that are minimally logged under the bulk-logged recovery model (and under the simple Recovery model), but that are exceptions when you run a backup.

Note

Memory-Optimized tables do not support minimal logging.

Note

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

The following operations perform full logging under the full recovery model, while logging is minimally logged under the simple and bulk-logged recovery model:

• Bulk Import operations (bcp, BULK Insert, and insert ... SELECT). For more information about when to minimally record a bulk import table, see prerequisites for Minimal Logging in Bulk Import.

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

• SELECT into operation.

When transactional replication is enabled, the SELECT into operation is fully logged, even under the bulk-logged recovery model.

• When inserting or appending new data, use the in the UPDATE statement. The WRITE clause is partially updated to the large value data type. Note that minimal logging is not used when updating existing values. For more information about large value data types, see Data types (Transact-SQL).

• In UpdateText, Nupdatetext, and UPDATETEXT, Nupdatetext, and UPDATETEXT statements. Note that minimal logging is not used when updating existing values.

  Important

  Using WRITETEXT statements and UPDATETEXT statements is not recommended, and you should avoid using these statements in new applications.

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

  • CREATE index operation (including indexed views).

  • ALTER INDEX REBUILD or DBCC dbreindex operation.

    Important

    The use of the DBCC dbreindex statement is deprecated, and should not be used in new applications.

  • DROP INDEX New Heap regeneration (if applicable). (the release action for the index page is always fully logged during the DROP index operation.) )

 

SQL Server Transaction transaction log