May 15, 2013 at 10:13 am
I suspect everything is hung up on figuring out how to accomplish a checkpoint. I don't understand, fundamentally, why I can't add a new log or grow an existing one. In the [sys].[dm_exec_requests] I have
- an UPDATE command waiting on LOGMGR_RESERVE_APPEND
- a CHECKPOINT command (background) waiting on CHECKPOINT_QUEUE
- a LOG WRITER command (background) waiting on LOGMGR_QUEUE
I start to suspect there is some sort of deadlock involved, causing everything to be stuck waiting for the checkpoint, and the checkpoint to be stuck because the log is full. But I'm not sure.
select [status], [command], [blocking_session_id], [wait_type], [wait_time], [last_wait_type], [total_elapsed_time], [reads], [writes], [transaction_isolation_level], [lock_timeout], [nest_level] from sys.dm_exec_requests where command like '%LOG' or command like '%CHECKPOINT%' or wait_type like '%LOG%'
I don't know how to post a table successfully 🙁
statuscommandblocking_session_idwait_typewait_timelast_wait_typetotal_elapsed_timereadswritestransaction_isolation_levellock_timeoutnest_level
backgroundLOG WRITER0LOGMGR_QUEUE51LOGMGR_QUEUE171,690,432002-11
backgroundCHECKPOINT0CHECKPOINT_QUEUE1100CHECKPOINT_QUEUE171,655,103112,0972-1-1
suspendedUPDATE0LOGMGR_RESERVE_APPEND447LOGMGR_RESERVE_APPEND2,102002-11
May 15, 2013 at 10:17 am
re: TEMPDB
My tempdb data has 19MB allocated of which 13MB is free, and its log has 5MB allocated of which 2MB is free.
DBCC LOGINFO shows it has 18 VLFs of which 4 are status 2, and the others are all status 0.
May 15, 2013 at 10:23 am
I've considered recreating the entire database, because I can successfully pull all the data out via Import/Export Wizard's SSIS. In fact I have a couple copies of the database data pulled out that way. However, there are timestamp fields involved, and I hate to lose all that data, and I know of no way to preserve all the timestamp field data.
Also, I have multiple databases in this same situation.
Really, I'd like to know how to solve the situation, in case it occurs on a production system.
May 15, 2013 at 10:27 am
You may also want to take a look at this thread on SSC -
http://www.sqlservercentral.com/Forums/Topic708053-146-1.aspx
Tommy
Follow @sqlscribeMay 15, 2013 at 10:45 am
Tommy Bollhofer (5/15/2013)
You may also want to take a look at this thread on SSC -http://www.sqlservercentral.com/Forums/Topic708053-146-1.aspx
Interesting.
checking mine:
select * from sys.sysprocesses where lastwaittype like '%LOG%' or lastwaittype like '%CHECKPOINT%' or cmd like '%LOG%' or cmd like '%CHECKPOINT%'
I have a CHECKPOINT cmd that is in lastwaittype 'LOGMGR_RESERVE_APPEND', shows CPU 20841, and its login time and last batch are both two days ago (probably at my last reboot), and has stmt_start=0, blocked=0, waittime=386.
May 15, 2013 at 2:18 pm
SQL2012SP1CU3 doesn't help. Actually it makes it much worse - now the databases fail recovery (b/c a checkpoint cannot be written, b/c the transaction logs are full) and cannot be brought online at all, except by using emergency mode.
May 16, 2013 at 4:08 am
Have you checked TempDB to see if that's full?
May 16, 2013 at 8:26 am
f242337 (5/15/2013)
re: TEMPDBMy tempdb data has 19MB allocated of which 13MB is free, and its log has 5MB allocated of which 2MB is free.
DBCC LOGINFO shows it has 18 VLFs of which 4 are status 2, and the others are all status 0.
May 16, 2013 at 8:29 am
f242337 (5/16/2013)
f242337 (5/15/2013)
re: TEMPDBMy tempdb data has 19MB allocated of which 13MB is free, and its log has 5MB allocated of which 2MB is free.
DBCC LOGINFO shows it has 18 VLFs of which 4 are status 2, and the others are all status 0.
That is not nearly enough space to do anything. You need to increase the size of your tempDB, or move it to a bigger hard drive.
May 16, 2013 at 10:32 am
I just increased it (tempdb) to 200MB data and 100MB log. I'll see if it grows upward from there.
May 16, 2013 at 11:50 am
Depending on the size of the logs to be shrunk, that might not be enough, but it's a good start. Try running another Checkpoint and checking all the DBCC stuff again and see where that gets you.
May 16, 2013 at 12:42 pm
Brandie Tarvin (5/16/2013)
Depending on the size of the logs to be shrunk, that might not be enough, but it's a good start. Try running another Checkpoint and checking all the DBCC stuff again and see where that gets you.
But, CHECKPOINT still fails with the same error
The transaction log for database 'SharePoint_Config' is full due to 'CHECKPOINT'.
Just to recap:
- Trying to add a new log file fails with that error
- Trying to grow the existing log file fails with that error
- Trying to backup the database (even 'Copy-only Backup') fails with that error
I wonder if it is in some sort of deadlock where the checkpoint won't complete due to lack of transaction log, and the log cannot be grown because of the checkpoint.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply