Skip to main content

Transaction Log Full

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).
  • 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.
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).

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.

Labels:

Comments

Post a Comment

Popular posts from this blog

Digging into a long running job

Revisiting the blogger to finally start again blogging about the SQL Server after a long long time.  This post is on analyzing and digging into what is causing a job significant time to complete. This will be a basic level of approach and I wish, I could have done better. But, its was enough in my case to get to a conclusion, with the knowledge I equip.   Issue : A job that's scheduled to run every three hours, was running for more than 48hours. Average job run time, 1.5hrs.  Analysis : First and foremost thing to do, is to find out on what session id is the job running on the instance. You may use the below query to fetch the session id of the job from the job name. declare @Var1 varchar(36) select @Var1=substring(REPLACE(CAST(job_id AS VARCHAR(36)),'-',''),17,16) from sysjobs where name =' JOB_NAME ' select * from sys.sysprocesses where program_name like '%'+@Var1+'%' You may get a single row or multiple rows with ...
Post a Comment
Read more

Deferred prepare could not be completed

This is usually an error that is encountered while querying a remote database on a different server instance which says OLEDB provider "SQLNCLI" for linked server "ABCD" returned message "Deffered prepare could not be completed" Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. This is the way I could rectify, though this is not the lone mistake that gives this error every time. Just set the default to the database that is being querying to the user through which the Linked server is connecting to the database.
4 comments
Read more

Incorrect SET Options with Filtered Indexes

Rebuild Index|Create Index|DML on a Table with Filtered Index fails with the error: The following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934).  The step failed. The Operation above might succeed when its through SSMS. But, would fail when the operation is from a SQL Agent Job. This is because, SQL Agent by default, do not have the SET Options enabled (ON). They are OFF. Reason: The following are the SET Options that are needed while operation on a table with a filtered index. Required SET Options for Filtered Indexes The SET options in the Required Value column are required whenever any of the following conditions occur: Create a filtered index. INSERT, UPDATE, DELETE, or MERGE operation modifies the data in a...
Post a Comment
Read more