SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transaction Log Full in Simple Recovery mode


Transaction Log Full in Simple Recovery mode

Author
Message
99zardoz
99zardoz
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 75

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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87069 Visits: 45267
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, MVP, M.Sc (Comp Sci)
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


f242337
f242337
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 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.)
f242337
f242337
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 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
f242337
f242337
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

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