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

recovery-modelsql servertransaction-log

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.

Best Answer

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);
Related Question