Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SQL2012SP1, simple recovery, transaction log full, can't add another, won't grow Expand / Collapse
Author
Message
Posted Tuesday, May 14, 2013 8:51 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 393, Visits: 2,684
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.
Post #1452903
Posted Wednesday, May 15, 2013 7:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 5,676, Visits: 6,488
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1453082
Posted Wednesday, May 15, 2013 10:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:35 PM
Points: 16, 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'

Post #1453208
Posted Wednesday, May 15, 2013 10:11 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 18,064, Visits: 16,099
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
Post #1453210
Posted Wednesday, May 15, 2013 10:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:38 AM
Points: 1,157, Visits: 3,270
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

Post #1453211
Posted Wednesday, May 15, 2013 10:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:35 PM
Points: 16, 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


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


Post #1453212
Posted Wednesday, May 15, 2013 10:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:35 PM
Points: 16, 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.
Post #1453215
Posted Wednesday, May 15, 2013 10:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:35 PM
Points: 16, 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.
Post #1453217
Posted Wednesday, May 15, 2013 10:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:38 AM
Points: 1,157, Visits: 3,270
You may also want to take a look at this thread on SSC -

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


Tommy

Post #1453219
Posted Wednesday, May 15, 2013 10:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:35 PM
Points: 16, 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.
Post #1453224
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse