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
Mr or Mrs. 500
Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)

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

Group: General Forum Members
Points: 92105 Visits: 9544
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
Mr or Mrs. 500
Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)

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

Group: General Forum Members
Points: 92105 Visits: 9544
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
Mr or Mrs. 500
Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)

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

Group: General Forum Members
Points: 92105 Visits: 9544
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
Mr or Mrs. 500
Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)

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