Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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"

share|improve this question
    
Can you increase the size of the log file? Or does that error out as well? –  Cougar9000 May 13 '13 at 2:26

4 Answers 4

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.

share|improve this answer

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: Autogrowth Settings

share|improve this answer

Thank you.

Unfortunately, I'm sorry, I forgot to include this info in my original post:

I cannot change the growth model of my existing log file - it fails with the error:

transaction log is full, with wait_desc of 'CHECKPOINT'

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

The current log growth model is +10%. The current log file is about 800MB, and the disk has 20GB+ free.

share|improve this answer
    
Please edit your question and add this information to it. Or for some reason, if you cannot, add it as a comment to the question. This should not be posted as an answer. –  StanleyJohns May 13 '13 at 14:19

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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