Applies To: SQL Server 2016
This topic discusses possible responses to a full transaction log and suggests how to avoid it in the future.
When the transaction log becomes full, SQL Server Database Engine issues a 9002 error. The log can fill when the database is online, or in recovery. If the log fills while the database is online, the database remains online but can only be read, not updated. If the log fills during recovery, the Database Engine marks the database as RESOURCE PENDING. In either case, user action is required to make log space available.
The appropriate response to a full transaction log depends partly on what condition or conditions caused the log to fill.
To discover what is preventing log truncation in a given case, use the log_reuse_wait and log_reuse_wait_desc columns of the sys.database catalog view. For more information, see sys.databases (Transact-SQL). For descriptions of factors that can delay log truncation, see The Transaction Log (SQL Server).
Under the full recovery model or bulk-logged recovery model, if the transaction log has not been backed up recently, backup might be what is preventing log truncation. If the log has never been backed up, you must create two log backups to permit the Database Engine to truncate the log to the point of the last backup. Truncating the log frees space for new log records. To keep the log from filling up again, take log backups frequently.
To create a transaction log backup
Increase the file size
If autogrow is disabled, the database is online, and sufficient space is available on the disk, either:
ADD LOG FILE.
Add a log file
This topic discusses possible responses to a full transaction log and suggests how to avoid it in the future.
When the transaction log becomes full, SQL Server Database Engine issues a 9002 error. The log can fill when the database is online, or in recovery. If the log fills while the database is online, the database remains online but can only be read, not updated. If the log fills during recovery, the Database Engine marks the database as RESOURCE PENDING. In either case, user action is required to make log space available.
The appropriate response to a full transaction log depends partly on what condition or conditions caused the log to fill.
To discover what is preventing log truncation in a given case, use the log_reuse_wait and log_reuse_wait_desc columns of the sys.database catalog view. For more information, see sys.databases (Transact-SQL). For descriptions of factors that can delay log truncation, see The Transaction Log (SQL Server).
IMPORTANT!!Alternatives for responding to a full transaction log include:
If the database was in recovery when the 9002 error occurred, after resolving the problem, recover the database by using ALTER DATABASE database_name SET ONLINE.
- Backing up the log.
- Freeing disk space so that the log can automatically grow.
- Moving the log file to a disk drive with sufficient space.
- Increasing the size of a log file.
- Adding a log file on a different disk.
- Completing or killing a long-running transaction.
Under the full recovery model or bulk-logged recovery model, if the transaction log has not been backed up recently, backup might be what is preventing log truncation. If the log has never been backed up, you must create two log backups to permit the Database Engine to truncate the log to the point of the last backup. Truncating the log frees space for new log records. To keep the log from filling up again, take log backups frequently.
To create a transaction log backup
IMPORTANT
If the database is damaged, see Tail-Log Backups (SQL Server).
Freeing disk space
You might be able to free disk space on the disk drive that contains the transaction log file for the database by deleting or moving other files. The freed disk space allows the recovery system to enlarge the log file automatically.Move the log file to a different disk
If you cannot free enough disk space on the drive that currently contains the log file, consider moving the file to another drive with sufficient space.IMPORTANT!! Log files should never be placed on compressed file systems.Move a log file
Increase log file size
If space is available on the log disk, you can increase the size of the log file. The maximum size for log files is two terabytes (TB) per log file.Increase the file size
If autogrow is disabled, the database is online, and sufficient space is available on the disk, either:
- Manually increase the file size to produce a single growth increment.
- Turn on autogrow by using the ALTER DATABASE statement to set a non-zero growth increment for the FILEGROWTH option.
NOTE In either case, if the current size limit has been reached, increase the MAXSIZE value.
Add a log file on a different disk
Add a new log file to the database on a different disk that has sufficient space by using ALTER DATABASEAdd a log file
Discovering long-running transactions
A very long-running transaction can cause the transaction log to fill. To look for long-running transactions, use one of the following:- sys.dm_tran_database_transactions. This dynamic management view returns information about transactions at the database level. For a long-running transaction, columns of particular interest include the time of the first log record (database_transaction_begin_time), the current state of the transaction (database_transaction_state), and the log sequence number (LSN) of the begin record in the transaction log (database_transaction_begin_lsn).
- DBCC OPENTRAN. This statement lets you identify the user ID of the owner of the transaction, so you can potentially track down the source of the transaction for a more orderly termination (committing it rather than rolling it back).
No comments:
Post a Comment