Msg 829, Level 21, State 1, Line 1
Question
Wednesday, September 12, 2012 4:20 PM
I'm a novice when it comes to SQL and I inherited this task because no one else can spell SQL, so please be gentle. Running SQL Server 2008 R2. I get this error
Msg 829, Level 21, State 1, Line 1
Database ID 7, Page (1:3332395) is marked RestorePending, which may indicate disk corruption. To recover from this state, perform a restore.
when running a working data deletion script which of course keeps me from clearing out the old data because of the error. I've done the google searches for solutions so I kind of have an idea what this is about but I haven't a clue as to how to resolve it without restoring. The problem is that I don't have a clean backup to restore from. I know this is kind of wide open but any suggestions would be greatly appreciated.
All replies (8)
Thursday, September 13, 2012 6:34 PM ✅Answered
Hello,
The good news is that Index ID 21 can be cleaned up by dropping it and creating it. Indexes with ID > 1 are non-clustered indexes (or xml, spatial, etc) and can be re-created. The bad news is that Index ID 1 IS your table (it's your clustered index) which means you're probably going to lose data.
The best way to recover from this is as Rohit has pointed out by restoring a KNOWN GOOD backup that doesn't have corruption, then copying as much data as possible out of the corrupted database into your new restored one. There are varying levels of recovery that can be done to get as much data as possible, but it's up to your business to decide how much time and effort should be put into that.
If you do what is pointed out above as #1, I will warn you that attempting to rebuild your clustered index (unless it's an online rebuild which requires enterprise edition) the table will be unavailable and I doubt the action will succeed as this is your actual data and it *may* fail on the logical phase of the rebuild. I've honestly never had the clustered index be corrupt so I can't say for sure what will or won't happen.
Since your clustered index is corrupt I'm guessing you will lose data. If this is not an option I would seek an engagement by a consultant or a team of consultants before attempting to do anything. Either way, I would stop all user activity for this database as if it is a memory or IO subsystem problem allowing processing to continue (don't shut down sql server or detach the database, just shut down the application).
I personally don't want to be responsible for the environment as these are my views and there is no warranty implied or provided. I gave my thoughts on it, but my final answer is to seek out a known professional that deal with SQL Server database corruption and recovery.
-Sean
Sean Gallardy, MCC | Blog | Twitter
Wednesday, September 12, 2012 7:25 PM
Hello,
Is there any output to the following query?
select * from msdb.dbo.suspect_pages
Are you using database mirroring or availability groups (2012)? If so, try running your statement again.
-Sean
Sean Gallardy, MCC | Blog | Twitter
Wednesday, September 12, 2012 7:50 PM
Thanks for your reply.
We are not using database mirroring or availability groups as far as I can tell.
The query returns:
database_id file_id page_id event_type error_count last_update_date
7 1 2401930 3 1 2011-05-22 15:10:17.353
7 1 2369829 7 692 2011-09-03 09:09:12.417
7 1 3332395 2 39 2012-01-09 17:41:04.293
7 3 3860397 1 2 2012-01-09 15:40:40.520
7 3 3860604 1 2 2012-01-09 15:40:40.577
7 3 3860575 1 2 2012-01-09 16:33:50.683
7 3 3860422 1 2 2012-01-09 17:01:18.103
7 3 3860423 1 2 2012-01-09 17:01:18.153
7 1 3379147 2 3 2012-01-09 17:41:10.760
7 3 3739027 2 3 2012-01-09 17:41:10.877
7 3 3860589 1 2 2012-01-09 16:33:50.747
7 3 3860598 1 2 2012-01-09 16:33:50.740
7 3 3860614 1 2 2012-01-09 16:33:50.693
7 3 3860615 1 2 2012-01-09 16:33:50.703
7 3 3860619 1 2 2012-01-09 16:33:50.727
7 3 3860626 1 2 2012-01-09 16:33:50.713
Wednesday, September 12, 2012 8:23 PM
Run the DBCC checkdb on databse & sahre the results.
If depands on checkdb result that you need to restore the databse or can recover it from corruption without restore.
Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!
Wednesday, September 12, 2012 8:58 PM
Hello,
Looking at the output there are a few interesting things to note:
1. event_type 1 means an 823 or 824 error occurred which is very bad. This means that there could be some underlying issue with your IO subsystem or memory causing corruption. On the other hand it could be a 3rd party program touching and moving parts of files it shouldn't be. These are all centered around the january 9th 2012 date on file 3 which means they've been around for a while and may or may not have been corrected.
2. event_type 2 means that there was a bad checksum for the page. This could be caused by memory failures or inconsistant disk writes/reads. These also revolve around the January 9th time frame.
3. event_type 3 means that there was a torn page detected, see #2. This was detected May 2011, more than a year ago.
4. event_type 7 means that it was deallocated by DBCC. This was probably from running DBCB CHECKDB WITH REPAIR_ALLOW_DATA_LOSS. This was sometime around September 3rd 2011 - almost 1 year ago.
When was the last time a DBCC CHECKDB was run on this database? There clearly seems to be some issues with it. If you have backups, I'm willing to bet those have the corruption as well.
As suggested, please run DBCC CHECKDB(Insert_DB_Name_Here) WITH ALL_ERRORMSGS, NO_INFOMSGS on the database and post the output.
-Sean
Sean Gallardy, MCC | Blog | Twitter
Thursday, September 13, 2012 11:22 AM
Thanks again for your help. Below are the results for DBCC CHECKDB :
Msg 8928, Level 16, State 1, Line 1
Object ID 1530488531, index ID 1, partition ID 72057594102546432, alloc unit ID 72057594161332224 (type In-row data): Page (1:3332395) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1530488531, index ID 1, partition ID 72057594102546432, alloc unit ID 72057594161332224 (type In-row data), page (1:3332395). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -6.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1530488531, index ID 1, partition ID 72057594102546432, alloc unit ID 72057594161332224 (type In-row data). Page (1:3332395) was not seen in the scan although its parent (1:2958991) and previous (1:3332394) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1530488531, index ID 1, partition ID 72057594102546432, alloc unit ID 72057594161332224 (type In-row data). Page (1:3332396) is missing a reference from previous page (1:3332395). Possible chain linkage problem.
Msg 8928, Level 16, State 1, Line 1
Object ID 1530488531, index ID 1, partition ID 72057594102546432, alloc unit ID 72057594161332224 (type In-row data): Page (1:3379147) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1530488531, index ID 1, partition ID 72057594102546432, alloc unit ID 72057594161332224 (type In-row data), page (1:3379147). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -6.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1530488531, index ID 1, partition ID 72057594102546432, alloc unit ID 72057594161332224 (type In-row data). Page (1:3379147) was not seen in the scan although its parent (1:2958991) and previous (1:3379146) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1530488531, index ID 1, partition ID 72057594102546432, alloc unit ID 72057594161332224 (type In-row data). Page (1:3379148) is missing a reference from previous page (1:3379147). Possible chain linkage problem.
Msg 8928, Level 16, State 1, Line 1
Object ID 1530488531, index ID 21, partition ID 72057594102480896, alloc unit ID 72057594161594368 (type In-row data): Page (3:3739027) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1530488531, index ID 21, partition ID 72057594102480896, alloc unit ID 72057594161594368 (type In-row data), page (3:3739027). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -6.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1530488531, index ID 21, partition ID 72057594102480896, alloc unit ID 72057594161594368 (type In-row data). Page (3:3739027) was not seen in the scan although its parent (3:4174311) and previous (3:3739026) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1530488531, index ID 21, partition ID 72057594102480896, alloc unit ID 72057594161594368 (type In-row data). Page (3:3802764) is missing a reference from previous page (3:3739027). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 12 consistency errors in table 'Keystroke' (object ID 1530488531).
CHECKDB found 0 allocation errors and 12 consistency errors in database 'SONAR'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (SONAR).
Thursday, September 13, 2012 6:13 PM
I will never suggest to go with repair_allow_data_loss, as it will casue data loss.
All curroption is with object id 1530488531 for 2 indexes, index id 1 & 21.
you have 3 possible solutions :-
1) Rebuild all indexes on table & run dbcc checkdb again
2) If step 1 not work then drop the indexes & recreate them and run dbcc checkdb again
3) If step 1 & 2 not work, restore all good full backup to have curroption free database.
Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!
Friday, September 14, 2012 3:16 AM
HI Bearsdad,
Based on the output displayed by using the DBCC CheckDB, it seems that some pages are damaged. I will recommend you to use the page restore, If your database meets the requirements of the page restore such as:
1. Page restore applies to SQL Server databases that are using the full or bulk-logged recovery models.
2. Only the database page can be restore.
Then we can use page restore to restore those damaged pages, the steps are as follow:
1. Obtain the page IDs of the damaged pages to be restored. In this thread page_id is 3332395.
2. Start a page restore with a full database, file, or filegroup backup that contains the page. In the RESTORE DATABASE statement, use the PAGE clause to list the page IDs of all of the pages to be restored.
3. Apply the most recent differentials backup.
4. Apply the subsequent log backups.
5. Create a new log backup of the database that includes the final LSN of the restored pages, that is, the point at which the last restored page is taken offline. The final LSN, which is set as part of the first restore in the sequence, is the redo target LSN. Online roll forward of the file containing the page is able to stop at the redo target LSN. To learn the current redo target LSN of a file, see the redo_target_lsn column of sys.master_files.
6. Restore the new log backup. After this new log backup is applied, the page restore is completed and the pages are now usable.
For example, if we want to restore the pages in the file 1, we know and the page IDs of the damaged pages are 2401930, 2369829 , 3332395, and 3379147. We can do：
RESTORE DATABASE <database> PAGE='1: 2401930, 1: 2369829, 1: 3332395, 1: 3379147'
FROM <file_backup_of_file_1>
WITH NORECOVERY;
RESTORE LOG <database> FROM <log_backup>
WITH NORECOVERY;
RESTORE LOG <database> FROM <log_backup>
WITH NORECOVERY;
BACKUP LOG <database> TO <new_log_backup>;
RESTORE LOG <database> FROM <new_log_backup> WITH RECOVERY;
GO
Please refer to:
Page restore: http://msdn.microsoft.com/en-us/library/ms175168.aspx .
Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.