December 12, 2012 at 10:14 am
I have a database that started giving me errors last night
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
SQL Server Assertion: File: <gfh.cpp>, line=2602 Failed Assertion = 'pageFull == 0'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
The transaction log for database 'database_name' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
the log_reuse_wait_desc indicates that it is waiting for a checkpoint. when I try to run a checkpoint i get the system assertion error above. when i try to increase the size of the transaction log file i get this error
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
DBCC checkdb returns
CHECKDB found 0 allocation errors and 0 consistency errors in database 'BOE_CMS'.
but I also get the following errors in the results
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 9002, Level 17, State 7, Line 1
The transaction log for database 'dbname' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
along with an error 824, which may indicate storage subsystem issue, and a stack dump which i have attached. I do have a backup from the previous evening which I can restore. I have checked with the SAN admins and they say there is nothing indicating disk issues.
I am looking to see if there is a way to recover from this. I should also note the server is SQL Server 2008 R2 SP1 CU4.
December 12, 2012 at 10:35 am
Can you take a log backup?
Do you have all the log backups since the full last night?
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
December 12, 2012 at 10:46 am
Forgot to mention that this database is in simple recovery, so no log backups. point-in-time recovery is not needed on the database so restoring from a couple of nights ago not that big of an issue. I am taking this situation as more of a learning experience.
December 12, 2012 at 12:25 pm
I'd suggest restore from backup then.
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
December 12, 2012 at 1:21 pm
Thanks. That is what I have done.
additionally I copied the mdf and corrupt ldf to a different server to poke around and when I try to attach the database the error I get is
The header file 'transactionlog_file.ldf' is not a valid database file header. The PageAudit property is incorrect. (Microsoft Sql Server, Error: 5172
In search around for this error i have found that this was probably an unrecoverable corruption.
December 12, 2012 at 2:12 pm
Yeah, that's pretty unrecoverable.
You may want to do some investigation of the IO subsystem, before it messes up again and corrupts the data file.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply