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

Question

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 (this is a test machine).

I've tried a few experiments with no luck, and done a fair bit of googling. 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.

Answer 1

Got it, help received from stackexchange.

https://dba.stackexchange.com/questions/241172/transaction-log-is-full-due-to-nothing-but-this-database-is-in-simple-recov?noredirect=1#comment475763_241172

Autogrowth was set to 0. Unfortunately there's no way to see this in SSMS because it hides such settings about recovery-mode-simple DBs.

Query to see the real value of Autogrowth, thanks to @HandyD:

SELECT 
    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
where mf.name like 'aspnetdb%'

The other problem is that, in this state you can't change autogrowth. But you can alter size. So by increasing size and then introducing autogrowth, you can fix the problem.

ALTER DATABASE aspnetdb MODIFY FILE (
    NAME = aspnetdb_log
    , SIZE = 1GB
) --this fixes the problem
GO
ALTER DATABASE aspnetdb MODIFY FILE (
    NAME = aspnetdb_log
    , SIZE = 1025MB
    , MAXSIZE = UNLIMITED
    , FILEGROWTH = 10MB
) -- now we have autogrowth
GO
USE aspnetdb
DBCC SHRINKFILE(aspnetdb_log,1) --now we can shrink the DB back to a sane minimum since autogrowth is in place
GO