Its often to have issues on transaction log getting full with an error:
The Transaction Log for the database ABC is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in the SYS.DATABASES System catalog view.
If we execute the following query in the query editor,
select log_reuse_wait_desc from sys.databases where name = 'ABC';
we get one of the follwing possibilities:
Reuse of transaction log space is currently waiting on one of the following:
0 = Nothing
Currently there are one or more reusable virtual log files.
1 = Checkpoint
No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file (all recovery models).
2 = Log backup
A log backup is required to move the head of the log forward (full or bulk-logged recovery models only).
Note: Log Backup do not prevent truncation
3 = Active backup or restore
data backup or a restore is in progress (all recovery models).
A data backup works like an active transaction, and, when running, the backup prevents truncation.
4 = Active transaction
A transaction is active (all recovery models).
Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database (full recovery model only).
6 = Replication
During transactional replications, transactions relevant to the publications are still undelivered to the distribution database (full recovery model only).
7 = Database snapshot creation
A database snapshot is being created (all recovery models).
8 = Log Scan
A log scan is occurring (all recovery models).
9 = Other (transient)
This value is currently not used.
In most of the cases, taking a transactional backup can fix the issue.
Other possible things that can be used are,
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.
Moving 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. (Log files should never be placed on Compressed file systems.)
The Transaction Log for the database ABC is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in the SYS.DATABASES System catalog view.
If we execute the following query in the query editor,
select log_reuse_wait_desc from sys.databases where name = 'ABC';
we get one of the follwing possibilities:
Reuse of transaction log space is currently waiting on one of the following:
0 = Nothing
Currently there are one or more reusable virtual log files.
1 = Checkpoint
No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file (all recovery models).
2 = Log backup
A log backup is required to move the head of the log forward (full or bulk-logged recovery models only).
Note: Log Backup do not prevent truncation
3 = Active backup or restore
data backup or a restore is in progress (all recovery models).
A data backup works like an active transaction, and, when running, the backup prevents truncation.
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. For more information, see "Long-Running Active Transactions," later in this topic.
- A transaction is deferred (SQL Server 2005 Enterprise Edition and later versions only). A deferred transaction is effectively an active transaction whose rollback is blocked because of some unavailable resource.
Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database (full recovery model only).
6 = Replication
During transactional replications, transactions relevant to the publications are still undelivered to the distribution database (full recovery model only).
7 = Database snapshot creation
A database snapshot is being created (all recovery models).
8 = Log Scan
A log scan is occurring (all recovery models).
9 = Other (transient)
This value is currently not used.
In most of the cases, taking a transactional backup can fix the issue.
Other possible things that can be used are,
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.
Moving 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. (Log files should never be placed on Compressed file systems.)
Increasing the Size of a Log File
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.
To 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.
Adding 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 DATABASE <database_name> ADD LOG FILE.
Comments
Post a Comment