What to do if the SQL Transaction Log is Full ?

The Transaction Log is a file that involves all transaction records and database modifications made by every single transaction in SQL Server. These log files are becoming an important part of SQL database when it comes to disaster recovery and it will not be in a corrupted state. In case of a disaster, you need to recover database with the availability of information in SQL transaction log. Each log file should be cleared or truncated regularly to keep log file size from filling up. Here, we are going to discuss a complete solution to deal with full SQL transaction log without any loss.

Let us suppose, if the Transaction Log file has become very large there is no space on hard disk and database is not working for all the data operations. Usually, the SQL Server will mark the database as suspect.

Many times the administrator has asked a question ""Why is database log file growing so strong?". The database file may be small, but day by day the log file is growing.

Here is the example in which you may receive an error message or find in log:

Parties Involved

SQL Server consists of two database files such as:

• Database file eg .mdf and
• Transaction-Logfile .ldf.

In addition to this, each file has the logical name. Just look at the below example:

select * from sys.database_files

Move to the physical files and the first primary database file is MDF. For large databases, it will split whole data into multiple parts (filegroups) – this may result in better performance on multiple disk drives.

Transaction Log File

Single Function of SQL Server is that all database actions are written in transaction log and then, in a database file. The data from transaction log to be carried onto database through the COMMIT function (when database transaction is complete). Thus, in Transactionlog, there are just "temporary" data or file, a kind of cache.

The transaction log file is divided into multiple segments known as "virtual logs".

SQL Server will take care of administration of the Transaction Log file itself. After you commit the transaction, checkpoint is set and the usable space is released again. Further, the already saved data would be truncated.

Depends on the recovery model and size or capacity of database, but, the size of transaction log is growing (always by a set of virtual log block).

Transactions are written in sequential order. In case, if the space is limited or end is reached, it can resume at beginning of a file (loop).

SQL Transaction Log is Full

When the transaction log becomes full, SQL Server refuses the query and even backups:

Now, execute the query "BACKUP DATABASE ..." failed with following error:

"The transaction log for database" is full. To figure out why space in log file can not be reused, check the log_reuse_wait_desc column in sys.databases BACKUP DATABASE is terminating abnormally.

What's next?

You can run this command "DBCC SQLPERF (logspace)". It will show how much space is consumed in transaction log file:

According to this example, cms database already use 91% of available space. It shhows a sign of a bottleneck. Thus, to reduce the transactions you need to take backup of Transaction Log.

So the SQL Transaction Log is written to backup file cms.trn:

Note:Once you have a backup copy, then you need to check again with DBCC SQLPERF. Also, you can perform TSQL command.

Simple Method to Shrink Transaction Log File

If the transaction log is growing rapidly then, you have to free up space. Hence, log truncation will help to clear space in log file so that the transaction log (TLog) can reuse it. Unless there is any kind of unexpected delay, log truncation occurs automatically after a log backup (if database is available in Simple recovery model) or checkpoint (if database is in Bulk or Full-logged recovery model). You can shrink the log file using TSQL command and follow the steps listed below:

• If database is in simple recovery model then, you can use below statement to shrink transaction log file:
DBCC SHRINKFILE(Test_DB,1)
• Replace TestDB_log with logical name of a log file you need to shrink and change 1 with a number of MB you want log file shrunk to.
• If database is in Full recovery model you can set to SIMPLE, run the DBCC SHRINKFILE command, and set back to FULL if you are caring about data loss in log.

• Note: You can search the logical name of log file by using below query:
  
• Another option to reduce log file using FULL recovery model is to backup the transaction log for the database using BACKUP LOG statement and then provide SHRINKFILE command to shrink the transaction log file:

Prevent Transaction Log from Becoming too large

Here are some tips to avoid this condition "SQL transaction log full" in future. Some of them are mentioned below:

• Backing up the Transaction log files.
• Free disk space so that log can automatically grow.
• Move the log file to disk drive with enough space.
• Increase the size of transaction log files.
• Add Log files on a separate disk or drive.
• killing a long-running transactions.

Time to Conclude

If the SQL transaction log becomes full, then you have to shrink the transaction log. So, in this blog, we have discussed a complete procedure to truncate SQL transaction log. Make sure you follow all the instructions very carefully without any data loss.