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'


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!



4 Answers 4


(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)

TO DISK = 'dbName_log_backup.trn'

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"

  • Thanks @Kin, once again for the good to know info. However, applying SP2 neither taking log backup (as model was in full recovery mode) worked. The thing which worked during the weekend, when 100 GB space was added to the drive and we did a manual checkpoint, in turn causing the growth in log which later was recovered to original.
    May 18, 2015 at 17:04
  • @KASQLDBA you told that Tried Manually doing CHECKPOINT on the DB, but still same error. Since manual CHECKPOINT did not work, how did it work - as you are telling ?
    – Kin Shah
    May 18, 2015 at 18:09
  • we only had 20 GB space left on the log drive. Over the weekend, 100 GB additional space was added on that log drive. After that team over the weekend, did manual checkpoint it worked for them.
    May 19, 2015 at 6:33

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> REBUILD LOG ON (NAME='<database>',FILENAME='<filename>')
ALTER DATABASE <database> set online
  • Nope its not fixed, growth is set to unlimited. I tried earlier adding the log file (second), but no success. Also, ,FILENAME='FILENAME', here the filename i believe is the log fiile path correct? Will let you know the results
    May 15, 2015 at 9:54
  • I appreciated this tip of using SET EMERGENCY, but in both the O.P.'s case and mine, the DB was in SIMPLE recovery model, thus, REBUILD LOG was/is not an option. But I was able to use emergency mode to do a CHECKPOINT and SHRINK so it was still a helpful tip. Also I was on 2016.
    – NateJ
    May 25, 2017 at 15:59

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>]  
-- Truncate the log by changing the database recovery model to SIMPLE.  
-- Shrink the truncated log file to 1 MB.   
DBCC SHRINKFILE ('<LogName>', 5);
-- Reset the database recovery model.  

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

Add 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.


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

Check using this command


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

    sdb.name as DbName
    ,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,

FROM sys.databases AS sdb 
WHERE log_reuse_wait > 0

And to resolve this use this..

select * from sys.dm_db_log_info(db_id('dbname'))

This helped me in my case.

Your Answer

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

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