Transaction log is full (due to NOTHING)… but this database is in simple recovery mode

All we need is an easy explanation of the problem, so here it is.

I’m supporting an antedeluvian webapp (soon to be retired) that still uses “aspnetdb” for its auth system. I was doing some work in prep for its retirement on my test environment, when I found my test server complaining with the following error:

The transaction log for database ‘aspnetdb’ is full due to ‘NOTHING’.

Now, normally I’d assume the problem came from the database transaction log… but this database was recently switched into simple recovery mode (our admin got sick of us complaining that the test-SQL-server was out of space and so she switched it to simple recovery).

I’ve tried a few experiments with no luck, and done a fair bit of googling. Other answers talk about growing the size of the transaction log… but all options related to the transaction log files and autogrowth are greyed out in SSMS – does the transaction log file even exist? I’ve tried setting the transaction log to ulimited size through

alter database aspnetdb modify file (NAME = 'aspnetdb_log', maxsize = UNLIMITED, FILEGROWTH = 100MB)

but that just fails with the same error.

Anybody seen this error before? Full transaction log on a database in simple recovery mode?

It’s on SQL Server 2016, running in 2008 compatibility mode because aspnetdb is that old.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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 your max and current size are equal this could be causing your issue. If so, try this command:

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply