SQL Clone
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
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4350 Visits: 5904
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
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37926 Visits: 9274
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
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 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 Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66701 Visits: 18570
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
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3954 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
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 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
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 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
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 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
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3954 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
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 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