none
A system assertion check has failed

    Question

  • Hello,

    We are using SQL Server Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) Enterprise Edition (64-bit) and getting below errors from previous some days.
    Please guid me how to resolve this issue.

    02/09/2018 11:09:33,spid625,Unknown,A system assertion check has failed. Check the SQL Server error log for details. Typically<c/> an assertion failure is caused by a software bug or data corruption. To check for database corruption<c/> consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup<c/> 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.
    02/09/2018 11:09:33,spid625,Unknown,Error: 3624<c/> Severity: 20<c/> State: 1.
    02/09/2018 11:09:33,spid625,Unknown,SQL Server Assertion: File: <SpcAlloc.cpp><c/> line=906 Failed Assertion = 'pPage->IsType (Page::TEXT_MIX_PAGE)'. This error may be timing-related. If the error persists after rerunning the statement<c/> use DBCC CHECKDB to check the database for structural integrity<c/> or restart the server to ensure in-memory data structures are not corrupted.

    Regards,

    Taoqir

    Friday, February 9, 2018 8:05 AM

Answers

  • I have run CHECKDB and below is the result. How to repair allocation and consistency errors?

    CHECKDB found 3 allocation errors and 12 consistency errors in database 'Test'.
    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Test).

    Regards,

    Taoqir

    Yes there is corruption in database and most probable cause should be bad disk. The information about same should be in the eventviewer please search thoroughly.

    Do you have clean consistent backup. How big is your database. As you can see minimum repair is repair_allow_data_loss and when you do that it may or may not remove corruption and may delete data and remove constraints. So if you have option of restoring from clean backup go ahead and do it. 

    If  this is test environment and you are ok with data loss you may run repair_allow_data_loss.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by Taoqir Friday, February 9, 2018 1:40 PM
    Friday, February 9, 2018 9:12 AM
    Moderator

All replies

  • I have run CHECKDB and below is the result. How to repair allocation and consistency errors?

    CHECKDB found 3 allocation errors and 12 consistency errors in database 'Test'.
    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Test).

    Regards,

    Taoqir

    Friday, February 9, 2018 8:13 AM
  • There are no simple answers for corruption issues. Some problems (the vast majority) aren't repairable, meaning you are in for a restore. This might serve as a starting point:

    http://karaszi.com/recommended-actions-for-corrupt-databases


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, February 9, 2018 9:04 AM
    Moderator
  • I have run CHECKDB and below is the result. How to repair allocation and consistency errors?

    CHECKDB found 3 allocation errors and 12 consistency errors in database 'Test'.
    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Test).

    Regards,

    Taoqir

    Yes there is corruption in database and most probable cause should be bad disk. The information about same should be in the eventviewer please search thoroughly.

    Do you have clean consistent backup. How big is your database. As you can see minimum repair is repair_allow_data_loss and when you do that it may or may not remove corruption and may delete data and remove constraints. So if you have option of restoring from clean backup go ahead and do it. 

    If  this is test environment and you are ok with data loss you may run repair_allow_data_loss.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by Taoqir Friday, February 9, 2018 1:40 PM
    Friday, February 9, 2018 9:12 AM
    Moderator
  • It's our production database and we can't bear data loss, we have took this matter with our storage vendor.

    Clean backups are available but restoring at this time and just want to find its root cause from storage aspect. 

    Regards,

    Taoqir

    Friday, February 9, 2018 1:40 PM