4

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.

Pxtl
  • 880
  • 8
  • 18
  • I would think in simple recovery, you would not see a growing log, however, if you have not backed up the database or caused some action to truncate the logs then perhaps the database was full to begin with and you are running up against auto-growth problems. In this situation, I would wonder does setting the recovery mode to simple shrink the logs? – Ross Bush Jun 22 '19 at 04:32
  • What, again? Like, set recovery simple even though it's already on recovery simple? I'll give it a shot... Nope, didn't work. I tried putting it back in FULL and that didn't work either. – Pxtl Jun 22 '19 at 04:36
  • 1
    I was wondering if toggling recovery mode would apply shrinkage to the log file. Let's say the log was super big and you were running out of space. Does going to simple recovery shrink the log file or would you have to take another action to do so? You might also want to post this over at --> https://dba.stackexchange.com/ – Ross Bush Jun 22 '19 at 04:40
  • Well, i've posted it there now too. Thanks for the tip. Unortunately I can't toggling out of simple recovery - seems like every operation triggers the complaint. – Pxtl Jun 22 '19 at 04:59
  • Have you tried shrinking the log file? – Ross Bush Jun 22 '19 at 05:05
  • Yes, shrinking the log file has no effect. – Pxtl Jun 22 '19 at 19:23

2 Answers2

5

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
Pxtl
  • 880
  • 8
  • 18
2

Even in simple recovery mode, you can still get a full transaction log. Simple recovery mode simply means that the transaction log is truncated after each completed transaction.

The transaction log still needs space to accomodate for all active transaction and all transactions that are being rolled back.

So one likely cause is that you still have an open transaction on your database. If that happens, the transaction log will not get truncated.

The other angle is the actual space availability. If you have configured your log file with a maximum file size, or when you are running out of disk space, you can run into this.

Gert-Jan
  • 327
  • 1
  • 9
  • I have 40GB of free disk space on this machine and the aspnetdb is comparatively tiny, and there are no open transactions. Even the smallest updates are failing. – Pxtl Jun 22 '19 at 19:22
  • Have you checked the autogrowth settings of the transaction log? Make sure it either has Unrestricted File Growth or some sensible value for Restricted File Growth (I would say at least 100 MB) – Gert-Jan Jun 23 '19 at 08:40