Is Your Transaction Log Full?

This Afternoon, I was contacted by a developer with an error message “The transaction log for database ‘database name’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc_column in sys.database [SQL STATE 42000] (Error 9002). The step failed.” The error message is pretty clear as why our SQL Server is throwing the error 9002. For each databases in SQL Server, there is at least one transaction log file and a data file. The log file records all the activities within the database. When the space allocated for a transaction log is full, the database engine cannot write to a log file and the database ceases

What can cause error 9002?

1. A long running query
2. Too many queries running at a same time.
3. The log file isn't truncated or backed up
4. The space allocated to log file is small
5. Full Recover model with no Transaction Log backup.

How do to fix the issue?
Like everyone else, I searched online for a solution and I landed on Microsoft  MSDN site. Their site recommends the following:

1. Backing up the log.
2. Freeing disk space so that the log can automatically grow.
3. Moving the log file to a disk drive with sufficient space.
4. Increasing the size of a log file.
5. Adding a log file to a different disk.
6. Completing or killing a long-running transaction.

How is cleaning a disk or moving a log file to a disk drive with sufficient space going to help resolve the issue?  The database transaction log file of our SQL Server is configured to use 9 GB of disk space. The database is set to use Full Recovery Model with a daily full backup and hourly transaction log backup. These backup jobs are running successfully which also means the truncates of log file are happening hourly. Since I had few unused space, I decided to add 6 additional GB and asked the developer to run his job. The problem didn't go away with additional space, it only took little longer to throw an error. Now, someone please explain me, what is the definition of sufficient space? How is the above recommendation going to help us when you don't know the exact size of your log file? There isn't a way to find out how big the log file should be, can you?

How I would tackle this?
We know the problem but we don't know what is causing it. The first step is to find out what is the root cause? Never ever jump into fixing a problem without knowing the root cause. The solution you are going to implement or may not fix your problem. Let's check the space available for log file using the query below.

Run the query below to get the size and space available for a Log and Data file.

SELECT name AS [File Name] , file_id, physical_name AS [Physical Name],
size/128 AS [Total Size in MB],size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0
AS [Available Space In MB]
FROM sys.database_files;

Output:
This will give you the total size of  your log and data file and space available. If the available space is low, that mean you reaching the size limit.

This below query tells us what is causing the error? This is what the error message is asking us to run.

select log_reuse_wait_desc
from sys.databases where name ='DB NAME';

Output:
ACTIVE TRANSACTION ( in our case!)

The above result says the query transaction is causing the log to be filled. This is the only query running on this database while we got this error. Why is my single query taking that much of disk space?  I checked the stored procedure which the job was calling. The first line of the procedures was  "delete from a table". I converted a delete into select statement to check the delete count and guess what what I found? The procedure was trying to delete 5 millions record from a table -No Kidding!!.
Now, you know what is going here, you can not do a massive delete in a single transaction. For every row it deletes, the SQL Server records the database activities into a transaction log file. This is why the log file gets filled up fast. The transactions should be committed after certain interval of delete to avoid the log fill.

How did I fix this?
I changed the DELETE query to use TRUNCATE.  The job ran fine with TRUNCATE and then everyone was happy when the job completed successfully. The truncate deletes all the data without logging it. Therefore, the truncate is much faster. It is very critical to know the difference between delete and truncate and when to use them. To solve any problem, you must find the root cause and then find the solution. This problem solving technique has always worked for me and I recommend this problem solving strategy to anyone.