6

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!

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)

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"

  • 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. – KASQLDBA May 18 '15 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 '15 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. – KASQLDBA May 19 '15 at 6:33
1

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
  • 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 – KASQLDBA May 15 '15 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 '17 at 15:59
0

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.

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.

DBCC SQLPERF(LOGSPACE);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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