January 16, 2019 at 11:53 am
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.
January 16, 2019 at 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.
January 16, 2019 at 7:04 pm
Luke L - Wednesday, January 16, 2019 2:49 PMIn 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
Change is inevitable... Change for the better is not.
February 4, 2019 at 6:03 am
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply