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
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
SQL2012SP1CU3 doesn't help. Actually it makes it much worse - now the databases fail recovery (b/c a checkpoint cannot be written, b/c the transaction logs are full) and cannot be brought online at all, except by using emergency mode.
Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15100 Visits: 9006
Have you checked TempDB to see if that's full?

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
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
f242337 (5/15/2013)
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.

Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15100 Visits: 9006
f242337 (5/16/2013)

f242337 (5/15/2013)
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.


That is not nearly enough space to do anything. You need to increase the size of your tempDB, or move it to a bigger hard drive.

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
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 just increased it (tempdb) to 200MB data and 100MB log. I'll see if it grows upward from there.
Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15100 Visits: 9006
Depending on the size of the logs to be shrunk, that might not be enough, but it's a good start. Try running another Checkpoint and checking all the DBCC stuff again and see where that gets you.

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
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
Brandie Tarvin (5/16/2013)
Depending on the size of the logs to be shrunk, that might not be enough, but it's a good start. Try running another Checkpoint and checking all the DBCC stuff again and see where that gets you.


But, CHECKPOINT still fails with the same error


The transaction log for database 'SharePoint_Config' is full due to 'CHECKPOINT'.


Just to recap:

- Trying to add a new log file fails with that error

- Trying to grow the existing log file fails with that error

- Trying to backup the database (even 'Copy-only Backup') fails with that error


I wonder if it is in some sort of deadlock where the checkpoint won't complete due to lack of transaction log, and the log cannot be grown because of the 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