Simple model database transaction log full 'CHECKPOINT'

Question

I have a SQL Server 2012 SP1 database which has a problem that the transaction log is full. It consists of a single data file and a single log file, both about 800MB.

I had 5GB free disk space, and I've enlarged it to 20GB+, just in case there was a disk space problem.

The database is in SIMPLE recovery model.

I cannot do a full database backup - it fails with

transaction log is full, with wait_desc of 'CHECKPOINT'

MTA: Note - the wait_desc is CHECKPOINT (someone asked below about the wait desc strings)

I cannot add a log file - it fails with the same error.

MTA: I cannot change the growth model of the one existing log file - it fails with the same error.

MTA: The existing log file has +10% growth.

MTA: I also cannot increase the size of the existing log file - it fails with the same error.

I cannot change the recovery model to full or bulk-logged - it fails with the same error.

I cannot execute a manual CHECKPOINT - it fails with the same error.

DBCC LOGINFO (dbid) shows that there are a couple hundred VLFs, all in status 2.

I'm not using change tracking, mirroring, or transaction log shipping.

What can I do to resolve this?

(As recommended, moved from stackoverflow to dba.stackexchange)

MTA: More tech details here (but no answers at all): http://www.sqlservercentral.com/Forums/Topic1451836-2799-1.aspx

eg, "DBCC OPENTRAN gives me no open transactions"

Answer 1

It sounds like you've got a huge transaction that has remained open which has done a lot of work. Run DBCC OPENTRAN on the database and see how long the oldest transaction has been open for. You'll probably need to kill that transaction (or have the user commit if it's someone in Management Studio). Then the log will clear automatically.

Answer 2

Check the autogrowth setting of the log file. Make sure it is smaller than the max file size and can grow. You can use this query for that:

SELECT DB_NAME(mf.database_id) database_name,
mf.file_id,
mf.type_desc,
mf.name,
LTRIM(STR(vfs.size_on_disk_bytes/1024.0/1024.0,30,3)) size_mb,
CASE
  WHEN mf.max_size = 0 OR mf.growth = 0 THEN '--'
  WHEN mf.max_size = -1 THEN 'unlimited' 
  ELSE LTRIM(STR(mf.max_size*8192.0/1024.0/1024.0,30,3)) 
END max_size_mb,
CASE WHEN mf.max_size = 0 OR mf.growth = 0 THEN 'none'
ELSE
  CASE mf.is_percent_growth
    WHEN 0 THEN LTRIM(STR(mf.growth*8192.0/1024.0/1024.0,30,3)) +' mb' 
    ELSE LTRIM(STR(mf.growth,4,0)) +'%'
  END 
END growth,
mf.physical_name
FROM master.sys.master_files mf
CROSS APPLY sys.dm_io_virtual_file_stats(mf.database_id,mf.file_id) vfs

You can change these settings on the files tab in the database properties dialog:

Answer 3

To start with - check the values of log_reuse_wait and log_reuse_wait_desc columns in the sys.databases catalog view. You can find if anything is preventing log truncation and act based on that. You can find a list of available values and their meaning here:

http://msdn.microsoft.com/en-us/library/ms190925.aspx

Answer 4

I had exactly the same problem, on a test system luckily. I tried all the same steps to no avail, but eventually a full restore from backup worked for me. Replication kicked in, but because the log was not yet full, I could expand the log size and allow it to grow whilst replication caught up.

My problem was compounded by a broken replication setup on our development environments that filled the transaction log and then got stuck in this endless loop of checkpoint and log full errors.