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 «««23456

Transaction Log Full in Simple Recovery mode Expand / Collapse
Author
Message
Posted Wednesday, October 5, 2011 7:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 10, 2014 8:39 AM
Points: 23, Visits: 39

Shrinkfile would have failed, the log header was corrupt. No amount of shrinking would help here, the problem was not that the log was too large, it was that the log was corrupt.


OK - my mistake - as as I said a question not a solution as such.


Do you like your database consistent and durable or corrupt with missing data? If the latter, sure, they're of little practical use. The point of the log is to ensure durability of transactions and consistency of data no matter what. The backing up of the log for point-in-time recovery is a secondary usage.


Given simple recovery, what can one actually do with the log file?

p.s. this issue is 8 months old and very likely resolved one way or another by now.


OK but people are still discussing it now and I got a link to it in my daily SQLServerCentral e-mail and the topic interested me. I'm here to learn as much as anything else so thought I'd ask a question.
Post #1185886
Posted Wednesday, October 5, 2011 8:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
99zardoz (10/5/2011)

Do you like your database consistent and durable or corrupt with missing data? If the latter, sure, they're of little practical use. The point of the log is to ensure durability of transactions and consistency of data no matter what. The backing up of the log for point-in-time recovery is a secondary usage.


Given simple recovery, what can one actually do with the log file?


Replication, transaction rollbacks, crash recovery. The first is a user-function, the other 2 system functions.

The ability to undo changes (rollbacks) and ensure consistent structure and data even in the case of a server failure (crash recovery) is the primary purpose of the log. Everything else (replication, log backups, mirroring, CDC) is a secondary use.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1185918
Posted Monday, May 13, 2013 11:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:35 PM
Points: 16, Visits: 34
I have what at least superfically seems similar.

Summary: I have a simple model database which is stuck due to an overfull transaction log - can't add more log space or another log file, or do much of anything, because it is stuck with a wait on 'CHECKPOINT'.

(Actually I have half a dozen in the same state, but I'm only concentrating on one, trying to find a solution.)

(My only guess is that it filled the log when there was no extra disk space, and some flag got set saying it is hopeless, and now I can't get that flag unset.)

Details:

I have a simple model database with a full transaction log (over 100% full). I have plenty of free disk space and the log is set to autogrow by 10% - the log file is c. 800MB and I have 20G+ free, so it growing by 80M should be quite possible.

Yet the log file won't grow, and I can't add another log file, and I can't adjust its size.

Everything fails with an error that the transaction is full, and the wait_desc is 'CHECKPOINT'.

DBCC OPENTRAN shows no open transactions. These are all SharePoint 2012 databases, and I've stopped & disabled the services involved (inetsvr etc) so there are no open user connections.

DBCC LOGINFO shows that I have over 200 VLFs, all in status 2 -- so it is indeed entirely full.

I can't execute a full backup (trying gives the same error - full transaction log due to 'CHECKPOINT')

Any suggestions?

(I posted a longer version of this elsewhere, but haven't received any help suggestions yet, I think.)
Post #1452235
Posted Monday, May 13, 2013 11:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:35 PM
Points: 16, Visits: 34
Note: SQL2012SP1. I have not applied CU2. This fix in CU2 sounds similar, but it says it is only for FULL recovery model.

http://support.microsoft.com/kb/2509302

Post #1452238
Posted Monday, May 13, 2013 11:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:35 PM
Points: 16, Visits: 34
One more note. DBCC CHECKDB doesn't report any specific errors, but it cannot create a checkpoint record (because the log is out of space...), and it cannot create a snapshot (same reason), and at the bottom it actually reports that the transaction log is full due to 'DATABASE_SNAPSHOT_CREATION' *and* that it is full due to 'CHECKPOINT'
Post #1452239
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse