Transaction log is full due to 'CHECKPOINT' for DB in simple recovery model

Question

For one of our DB's on SQL server 2012 RTM,

We could not perform almost any troubleshooting step as the error says:

" Transaction log is full due to 'CHECKPOINT"

This being a dev database, its backup never happened, so we are out of option with that even.

Tried shrinking the log file but no success and got below error:

log is out of space - with a cascade error that the transaction log is full due to 'CHECKPOINT'

DBCC CHECKDB mostly just gives errors because the transaction log is full due to 'CHECKPOINT'

I can't change the recovery model to full - get an error because the transaction log is full due to 'CHECKPOINT'

Cannot take backup: same error:

Even tried detach and attaching back on other server (SQL server 2012 RTM) , but same error:

Tried Manually doing CHECKPOINT on the DB, but still same error:

Have already refred to this post here Simple model database transaction log full 'CHECKPOINT'

and

The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT', but no success.

Note: We do not have replication set up on the current server or any database.

Please help as i am unable to find a root cause and fix for this!

Thanks!

Answer 1

(since this is longer than a comment ... so putting it as answer)

For one of our DB's on SQL server 2012 RTM.

This is your problem. There were many fixes introduced in the consecutive CUs / SPs after the RTM build.

Can you patch the sql server with the latest SP2 ? After patching, check if the issue still happens or not.

Your problem might be because your MODEL database might be in simple recovery.

If the model database is set to SIMPLE recovery model and user database is created with the simple recovery model from model database template, SQL Server does not truncate its log automatically like it suppose to (after a full backup). It appears that somehow SQL Server is treating it as if it is in full recovery model.

you can execute below log backup (even though your database is in simple recovery - as since model is in simple recovery - due to a bug in RTM, sql server treats it as being in FULL recovery)

BACKUP LOG dbName
TO DISK = 'dbName_log_backup.trn'
GO

Check this KB: Database does not follow simple recovery model behavior in SQL Server 2012 after you set the recovery model of the "model" database to "Simple"

Answer 2

Just making sure - Is the log file set to a fixed size? If so, try adding a secondary log file to the database and then do a checkpoint. If all else fails rebuild the log.

ALTER DATABASE <database> set  EMERGENCY 
ALTER DATABASE <database> REBUILD LOG ON (NAME='<database>',FILENAME='<filename>')
ALTER DATABASE <database> set online

Answer 3

I had the same problem with SQL 2016.

I tried the following but it also did not allow to modify DB with same XTP_CHECKPOINT error.

USE [<DBName>]  
GO  
-- Truncate the log by changing the database recovery model to SIMPLE.  
ALTER DATABASE [<DBName>] 
SET RECOVERY SIMPLE;  
GO  
-- Shrink the truncated log file to 1 MB.   
--checkpoint;
DBCC SHRINKFILE ('<LogName>', 5);
ALTER DATABASE [<DBName>] 
MODIFY file (NAME = '<LogName>', MAXSIZE = UNLIMITED, FILEGROWTH = 5MB);  
GO  
-- Reset the database recovery model.  
ALTER DATABASE [<DBName>] 
SET RECOVERY FULL;  
GO 

The only thing worked for me to recover from this state was to add a new transaction log file.

Once it is added, we can use the above query to shrink log file, if needed. But it is recommended to check the root cause on why the transaction log file is growing rapidly.

Tip: Try using the below query to check log usage for all databases.

DBCC SQLPERF(LOGSPACE);

Answer 4

Above all answers may work or not, If your log space is 100% utilized

Check using this command

DBCC SQLPERF(LOGSPACE);

And wait type is CHECKPOINT.(log_reuse_wait = 1) Check using below query..

SELECT 
    sdb.name as DbName
    ,sdb.log_reuse_wait
    ,sdb.log_reuse_wait_desc
    ,log_reuse_wait_explanation = CASE
        WHEN log_reuse_wait = 1 THEN 'No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond'
        WHEN log_reuse_wait = 2 THEN 'A log backup is required before the transaction log can be truncated.'
        WHEN log_reuse_wait = 3 THEN 'A data backup or a restore is in progress (all recovery models). Please wait or cancel backup'
        WHEN log_reuse_wait = 4 THEN 'A long-running active transaction or a defferred transaction is keeping log from being truncated. You can attempt a log backup to free space or complete/rollback long transaction'
        WHEN log_reuse_wait = 5 THEN 'Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database. (Full recovery model only)'        
        WHEN log_reuse_wait = 6 THEN 'During transactional replication, transactions relevant to the publications are still undelivered to the distribution database. Investigate the status of agents involved in replication or Changed Data Capture (CDC). (Full recovery model only.)'        

        WHEN log_reuse_wait = 7 THEN 'A database snapshot is being created. This is a routine, and typically brief, cause of delayed log truncation.'
        WHEN log_reuse_wait = 8 THEN 'A transaction log scan is occurring. This is a routine, and typically a brief cause of delayed log truncation.'
        WHEN log_reuse_wait = 9 THEN 'A secondary replica of an availability group is applying transaction log records of this database to a corresponding secondary database. (Full recovery model only.)'
        WHEN log_reuse_wait = 13 THEN 'If a database is configured to use indirect checkpoints, the oldest page on the database might be older than the checkpoint log sequence number (LSN).'
        WHEN log_reuse_wait = 16 THEN 'An In-Memory OLTP checkpoint has not occurred since the last log truncation, or the head of the log has not yet moved beyond a VLF.'
    ELSE 'None' END,
    sdb.database_id,
    sdb.recovery_model_desc

FROM sys.databases AS sdb 
WHERE log_reuse_wait > 0

And to resolve this use this..

USE dbname; CHECKPOINT
select * from sys.dm_db_log_info(db_id('dbname'))

This helped me in my case.