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 Wednesday, May 15, 2013 2:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:35 PM
Points: 16, 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.
Post #1453271
Posted Thursday, May 16, 2013 4:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:46 AM
Points: 5,676, Visits: 6,488
Have you checked TempDB to see if that's full?

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 #1453403
Posted Thursday, May 16, 2013 8:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:35 PM
Points: 16, 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.
Post #1453578
Posted Thursday, May 16, 2013 8:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:46 AM
Points: 5,676, Visits: 6,488
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 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 #1453581
Posted Thursday, May 16, 2013 10:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:35 PM
Points: 16, Visits: 34
I just increased it (tempdb) to 200MB data and 100MB log. I'll see if it grows upward from there.
Post #1453664
Posted Thursday, May 16, 2013 11:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:46 AM
Points: 5,676, Visits: 6,488
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 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 #1453713
Posted Thursday, May 16, 2013 12:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

Add to briefcase «««123

Permissions Expand / Collapse