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"
Best Answer
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.