1

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.

Improve this question
7
  • Have you checked the underlying disk has space? Yes, the transaction log definitely exists if your database is accessible.
    – George.Palacios
    Jun 22, 2019 at 7:51
  • Possible duplicate of dba.stackexchange.com/questions/178016/…
    – Learning_DBAdmin
    Jun 22, 2019 at 10:53
  • @george, yes, underlying disk has space.
    – Pxtl
    Jun 22, 2019 at 19:17
  • 1
    @learning_dbadmin I've reviewed that question and the only answer is unaccepted, does not describe how to do the things it mentions, and my attempts to manipulate the growth of the logfile all result in the same error. Basically every action that I attempt on this db (other than SELECT), including attempting to increase the size of the log, results in the given error.
    – Pxtl
    Jun 22, 2019 at 19:18
  • Is the database file on the C: drive? How much free space is there on the disk? I would try taking a full backup also. Please provide size details of all the db files: SELECT * FROM sys.master_files
    – Jhunter1
    Jun 23, 2019 at 9:02

1 Answer 1

Reset to default
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);
Improve this answer
2
  • Ooh, this looks promising. I ran it and found GROWTHINCREMENT set to 0 MB. That's a huge red flag. However, when I try to update it with ALTER DATABASE aspnetdb MODIFY FILE ( NAME = aspnetdb_log , SIZE = 10MB , MAXSIZE = UNLIMITED , FILEGROWTH = 10MB ) it errors out with the same error "The transaction log for database 'aspnetdb' is full due to 'NOTHING'." - I'm like "obviously, that's what I'm trying to fix here".
    – Pxtl
    Jun 24, 2019 at 21:03
  • Okay, it worked! Sort of. MAXSIZE failed. But that got me trying combinations, and I found the right mix - first set the SIZE and nothing else. Then set sane autogrowth parameters, then shrink it! sql ALTER DATABASE aspnetdb MODIFY FILE ( NAME = aspnetdb_log , SIZE = 1GB ) --this fixeds the problem GO ALTER DATABASE aspnetdb MODIFY FILE ( NAME = aspnetdb_log , SIZE = 1025MB , MAXSIZE = UNLIMITED , FILEGROWTH = 10MB ) GO USE aspnetdb DBCC SHRINKFILE(aspnetdb_log,1) GO
    – Pxtl
    Jun 24, 2019 at 21:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.