Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL2012SP1, simple recovery, transaction log full, can't add another, won't grow


SQL2012SP1, simple recovery, transaction log full, can't add another, won't grow

Author
Message
patrickmcginnis59 10839
patrickmcginnis59 10839
SSC Eights!
SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)

Group: General Forum Members
Points: 871 Visits: 5080
SQLRNNR (5/14/2013)
patrickmcginnis59 10839 (5/14/2013)
SQLRNNR (5/14/2013)
I have not run into that bug with this release (2012 SP1). But it appears that it is out there and there is potential for it.
But it is safe to assume that since there is a fix for it in a CU, that you could end up in a situation where you will not be able to release log space without the CU.


The reason I asked about just increasing the log size is that it was my understanding that increasing the log file size would add log space instead of trying to release existing log space (as they're probably in use), so I was seeing if my understanding was incorrect. Wouldn't be the first time!


Operations of that nature are one at a time. So if a checkpoint is in progress, it has to complete in order for the file growth to be able to happen. The checkpoint (in laymans terms) releases log space. The file growth would be adding space.

I'm just trying to think of it from a systems standpoint. The checkpoint is no longer in progress (sounds like it never even started according to the OP), so I get that, and its no longer going to free any more log records (virtual log files I guess). So what stops SQL server from making more space for more log records? Heck, looking at the original post he really had plenty of log space it seems. Even the operations of that nature seems like it does not have to progress one at a time to me even in a single threaded program you can mark your place after a fixed amount of progress with a task, go allocate some resources, and pick up where you left off. I could certainly understand if its an SQL Server specific situation (like not resizing the tempdb on a busy system) This sounds serious enough that I think its ok if you can describe it somewhat beyond lay terms, I'm pretty sure I've just landed on this version so I know I'd like to know all I can!

From Microsoft:

log_reuse_wait_desc value CHECKPOINT

No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file. (All recovery models)

This is a routine reason for delaying log truncation. For more information, see Database Checkpoints (SQL Server).


In the cited article, these log records weren't released even thought T-LOGS were taken under a full recovery model. But theres no mention that the T-LOGS failed to be made. In this case, CHECKPOINT itself fails!

For the original poster, have you been running checkdb's routinely? Just curious given the error messages.

to properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7745 Visits: 8719
This may or may not have anything to do with the problem but... Have you checked TempDB?

I've run into issues where the TempDB log is full up and won't let me do operations on other databases until TempDB is cleared & shrunk.

This is not something I recommend for the unwary, BTW. But it is a note of interest that the Shrinking operation often requires more space during its run.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
f242337
f242337
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 34
re: SQL2012SP1CU2 & stuck transaction log KB: http://support.microsoft.com/kb/2509302

I considered that, but

#1) That KB says it is for databases in Full Recovery Model

#2) It says it occurs as a result of a script run during upgrade. My stuck databases were all created on SQL2012.


This issue occurs because the "tran_sp_MScreate_peer_tables" transaction was left open by a replication upgrade script during the upgrade. This open transaction prevents usual log truncation.


#3) Following along the diagnostic steps given further down in the article

I don't even have this registry key: HKLM\SOFTWARE\Microsoft\MSSQLServer\Replication

and I get nothing back from


select * from sys.dm_tran_active_transactions where name = 'tran_sp_MScreate_peer_tables'

SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21063 Visits: 18258
Brandie suggested looking at the tempdb and expanding it if it is out of space.

Also, in my post, I recommended running the CU whether or not you have upgraded (which would invoke the upgrade scripts).

Try the tempdb thing and then be prepared to still apply that CU.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Tommy Bollhofer
Tommy Bollhofer
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1166 Visits: 3359
Good point. There are other options available. Have you consider the potential for data loss? The following thread may be helpful.

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/76e99d3a-0d6b-4ee3-8f22-6d56c3c6e50c/

Tommy

Follow @sqlscribe
f242337
f242337
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 34
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 Sad


status   command   blocking_session_id   wait_type   wait_time   last_wait_type   total_elapsed_time   reads   writes   transaction_isolation_level   lock_timeout   nest_level
background   LOG WRITER   0   LOGMGR_QUEUE   51   LOGMGR_QUEUE   171,690,432   0   0   2   -1   1
background   CHECKPOINT   0   CHECKPOINT_QUEUE   1100   CHECKPOINT_QUEUE   171,655,103   11   2,097   2   -1   -1
suspended   UPDATE   0   LOGMGR_RESERVE_APPEND   447   LOGMGR_RESERVE_APPEND   2,102   0   0   2   -1   1


f242337
f242337
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 34
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.
f242337
f242337
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 34
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.
Tommy Bollhofer
Tommy Bollhofer
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1166 Visits: 3359
You may also want to take a look at this thread on SSC -

http://www.sqlservercentral.com/Forums/Topic708053-146-1.aspx

Tommy

Follow @sqlscribe
f242337
f242337
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 34
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search