Error 9002. The transaction log for database is full due to 'NOTHING'

Question

In the SQL Server Error Log (and Windows Event Viewer) I found the error message:

The transaction log for database [...] is full due to 'NOTHING'.

• The database is in SIMPLE recovery model.
• Its log has 126 VLFs.
• It is a user database, not a system one.

What does a reason of "NOTHING" mean?

If there is no reason which prevents the log from being reused, then why does this error message exist?

Answer 1

NOTHING is basically a 'Not any of the other reasons' status. Other Log Full status values I've seen are: LOG_BACKUP, REPLICATION, and CHECKPOINT.

As to the cause, check the log file max size & growth properties. Then compare them to the available disk free space. The log file may have reached the Max size, or the disk may not have enough free space to accommodate the next growth interval/amount.

Answer 2

This question seems to be asked long back and OP hasn't put bounty to get the answer or attention from community users.

Below answer is in addition to correct answer posted by J. Wick

Kindly run the below script to check the size, max size and growth increments of your log files:

SELECT 
    @@SERVERNAME AS [Server],
    db.name AS [Database],
    mf.name AS [File],
    CASE mf.[type_desc]
        WHEN 'ROWS' THEN 'Data File'
        WHEN 'LOG' THEN 'Log File'
    END AS [FileType],
    CAST(mf.[size] AS BIGINT)*8/1024 AS [SizeMB],
    CASE
        WHEN mf.[max_size] = -1 THEN 'Unlimited'
        WHEN mf.[max_size] = 268435456 THEN 'Unlimited'
        ELSE CAST(mf.[max_size]*8/1024 AS NVARCHAR(25)) + ' MB'
    END AS [MaxSize],
    CASE [is_percent_growth]
        WHEN 0 THEN CONVERT(VARCHAR(6), CAST(mf.growth*8/1024 AS BIGINT)) + ' MB'
        WHEN 1 THEN CONVERT(VARCHAR(6), CAST(mf.growth AS BIGINT)) + '%'
    END AS [GrowthIncrement]
FROM sys.databases db
LEFT JOIN sys.master_files mf ON mf.database_id = db.database_id

If the max size and current size are equal this could be causing the underlying issue. If so, try below command to increase the size:

ALTER DATABASE [db_name] MODIFY FILE (NAME = 'file name', MAXSIZE = <new max size> GB);

Please keep in mind that only one command you can run at a time for increasing the size and growth:

ALTER DATABASE [db_name] MODIFY FILE ( NAME = 'file name' , SIZE = 1GB )

Next you can use below command for autogrowth(if you want to change this):

ALTER DATABASE [db_name] MODIFY FILE ( NAME = 'file name' , SIZE = 1025MB , MAXSIZE = UNLIMITED , FILEGROWTH = 10MB )

Afterwards if you wish to shrink log file since its in Simple recovery model, you may use below command:

USE db_name 
go
DBCC SHRINKFILE(file_name,1) 
GO 

Same is answered by HandyD. I have incorporated answer as well as comment from that question, so it would be complete answer.

Hope above helps.