Large Suspect Database

  • Hello, I have a large (~1.5TB) database that is in suspect mode.  Long story short we had some I/O issues that have hopefully be resolved.  To recover from the I/O pressure we were seeing, the decision was made to reboot the server.  When the instance came back up, one of the databases was suspect.

    The following is listed in the SQL LOG:
    2019-01-16 10:33:19.87 spid34s  Error: 824, Severity: 24, State: 2.
    2019-01-16 10:33:19.87 spid34s  SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x51b99696; actual: 0x462ec176). It occurred during a read of page (3:53047454) in database ID 11 at offset 0x0000652e13c000 in file 'V:\MSSQL\Data\MyDB\MyDB_Data.NDF'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
    2019-01-16 10:33:19.87 spid34s  Error: 3313, Severity: 21, State: 5.
    2019-01-16 10:33:19.87 spid34s  During redoing of a logged operation in database 'MyDB', an error occurred at log record ID (31151:5084:465). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
    2019-01-16 10:33:19.96 spid64s  Error: 824, Severity: 24, State: 2.
    2019-01-16 10:33:19.96 spid64s  SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x3720f748; actual: 0x50f684ca). It occurred during a read of page (3:72215104) in database ID 11 at offset 0x000089bd480000 in file 'V:\MSSQL\Data\MyDB\MyDB_Data.NDF'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
    2019-01-16 10:33:19.96 spid67s  Error: 824, Severity: 24, State: 2.
    2019-01-16 10:33:19.96 spid67s  SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xc48a2148; actual: 0xa35d28ca). It occurred during a read of page (3:72211412) in database ID 11 at offset 0x000089bb7a8000 in file 'V:\MSSQL\Data\MyDB\MyDB_Data.NDF'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
    2019-01-16 10:33:19.96 spid64s  Error: 3313, Severity: 21, State: 5.
    2019-01-16 10:33:19.96 spid64s  During redoing of a logged operation in database 'MyDB', an error occurred at log record ID (31151:1216:61). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
    2019-01-16 10:33:19.96 spid67s  Error: 3313, Severity: 21, State: 5.
    2019-01-16 10:33:19.96 spid67s  During redoing of a logged operation in database 'MyDB', an error occurred at log record ID (31151:6524:386). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.

    I have a backup that I would like to use to restore this database, however it is still in RECOVERY There is a spid blocking all access to this data including putting it in Emergency Mode, Offline, Deleting etc. It is a system SPID running the DB STARTUP command with a wait type of "PARALLEL_REDO_FLOW_CONTROL"  It's at 60.2 % complete and not moving forward for the past 2 hours.  It's been 4 hours since the instance came back online.

    I know that I can stop the instance and move the existing files and restart it so the database is completely inaccessible to get it into a state where I can restore.  Because there are other database on this system I want to keep available, is there another way to get this to complete or in some other way get the database to a state where I can perform a restore?

    Thanks,
    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • In the end as we were heading toward the end of the business day, we shut down the instance, deleted the original files, restarted the instance where the database came up as recovery pending.  At that point we were able to delete it and restore from a valid a backup.

    Next we'll run Check DB's on all of the other databases to ensure there are no hidden issues.
    Thanks,
    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • Luke L - Wednesday, January 16, 2019 2:49 PM

    In the end as we were heading toward the end of the business day, we shut down the instance, deleted the original files, restarted the instance where the database came up as recovery pending.  At that point we were able to delete it and restore from a valid a backup.

    Next we'll run Check DB's on all of the other databases to ensure there are no hidden issues.
    Thanks,
    -Luke.

    I believe the original problem may have been solved by changing the SQL Server Service to do a "delayed start".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Here are the causes of getting SQL error 824 and the solution around them:
    https://www.sqlserverlogexplorer.com/sql-error-824/

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply