The process in SQL Server "Log truncation" deletes inactive virtual log files 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 were never truncated, it would eventually fill all the disk space that is allocated to its physical log files.
The way this truncation happens is dependent on the recovery model that was selected for your database.
In the full or bulk-logged recovery model when the checkpoint occurs, all dirty pages are written to the disk, but SQL Server will not mark any of the virtual log files as reusable.
The transaction log will be truncated only after transaction log backup is completed.
To check what is preventing your log truncation by querying the log_reuse_wait and log_reuse_wait_desc columns of the sys.databases catalog view.
select name,log_reuse_wait_desc from sys.databases
If a transaction log were never truncated, it would eventually fill all the disk space that is allocated to its physical log files.
The way this truncation happens is dependent on the recovery model that was selected for your database.
Simple Recovery Model:
The transaction log is truncated when checkpoint occurs. At that time, all dirty pages are written to the disk and virtual log files can be reused.
Below picture shows a transaction log that has never been truncated.
Currently, four virtual log files are in use by the logical log. The logical log starts at the front of the first virtual log file and ends at virtual log 4.
Virtual log 1 and virtual log 2 contain only inactive log records. because transaction 11 and 12 have been committed. These records can be truncated. Virtual log 5 is still unused and is not part of the current logical log.
Currently, four virtual log files are in use by the logical log. The logical log starts at the front of the first virtual log file and ends at virtual log 4.
Virtual log 1 and virtual log 2 contain only inactive log records. because transaction 11 and 12 have been committed. These records can be truncated. Virtual log 5 is still unused and is not part of the current logical log.
Below picture shows after the checkpoint occurs, virtual log files 1 and 2 are no longer in use because transaction 11 and 12 have been committed. SQL Server marks virtual log file 1 and 2 as reusable.
The logical log now starts at the beginning of virtual log 3. Virtual log 5 is still unused, and it is not part of the current logical log.
After Check point all committed transactions have been truncated, but the physical size of the transaction log remains the same.
If there is a need to make transaction log file smaller, enable turn AUTO_SHRINK option it will physically shrink the log file (where possible) at periodic intervals: or manually Shrink the log file.
The logical log now starts at the beginning of virtual log 3. Virtual log 5 is still unused, and it is not part of the current logical log.
After Check point all committed transactions have been truncated, but the physical size of the transaction log remains the same.
If there is a need to make transaction log file smaller, enable turn AUTO_SHRINK option it will physically shrink the log file (where possible) at periodic intervals: or manually Shrink the log file.
If there is a need to make transaction log file smaller, enable turn AUTO_SHRINK option it will physically shrink the log file (where possible) at periodic intervals: or manually Shrink the log file.
Full or Bulk-Logged Recovery Models:
In this case, the transaction log is truncated only during the transaction log backup. In the full or bulk-logged recovery model when the checkpoint occurs, all dirty pages are written to the disk, but SQL Server will not mark any of the virtual log files as reusable.
The transaction log will be truncated only after transaction log backup is completed.
Factors that can delay log truncation:
Log truncation can be delayed by a variety of reasons. To check what is preventing your log truncation by querying the log_reuse_wait and log_reuse_wait_desc columns of the sys.databases catalog view.
select name,log_reuse_wait_desc from sys.databases
No comments:
Post a Comment