May 3, 2011 at 3:35 am
Hello Guys,
I'll try to describe my problem briefly.
My database is a SQL 2005 moved to SQL 2008 R2 (I restored a full S2005 backup under S2008 R2).
Recovery in simple mode. DB size is around 800GB.
After a mass bulk insert, I got the mentionned error while rebuilding indexes. The table concerned is partioned into data 76 files.
As the corruption was identified on one specific datafile, I tried:
- checkdb checktable (my_table) > failed because clustered index is disabled on my_table (don't ask me why I did that silly index disabled...)
- drop data file > failed because it's not empty of course
- delete my_table where my clause would delete the data file data > failed because no plan can be generated as clustered index is disabled
- DBCC CheckDB (<myDB>) WITH NO_INFOMSGS, ALL_ERRORMSGS > it is now suspended for hours on the DBCC SSB CHECK command (PAGEIOLATCH_EX wait).
I have a full backup that would be useless because I would lose my bulked inserted data...
Would anyone have a suggestion?
> I guess my IO subsystem si slow but anyway, is there a good way to work around that data corruption?
Thanks,
Olivier
May 3, 2011 at 3:39 am
Wait. If you need the results from checkDB, then you must wait for them.
Is the backup clean or does it also contain the corruption?
Can you restore the backup (assuming it's clean) and then re-run the bulk inserts? Restoring from backup (full, filegroup, file or page) is usually the recommended route, though would be best to wait and see what checkDB returns before deciding that.
Full recovery? Log backups?
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
May 3, 2011 at 3:46 am
Wait : The worse answer I was fearing... but expecting it 🙂
So yes I have a full backup but I'm not sure it is safe...
Recovery is simple, no log backups and I can't reproduce the bulk load as source data has already been changed...
Is there a way to recover a full backup except one file?
Thanks anyway for the quick answer!
Olivier
May 3, 2011 at 4:29 am
In full recovery you could restore single files. In simple, no.
Can you restore the backup to a dev/test server and run checkDB on it? If it's also corrupt, there's big problems...
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
May 3, 2011 at 5:51 am
Yes, I'll do that.
I'll wait till the end of my checkdb and start the restore to a new db afterwards.
Thanks!
May 4, 2011 at 8:37 am
After more than 20 hours of suspended/slow process, I found that the newlyu created database has its service broker disabled.
As the original database is using Service Broker, I guess checkdb was checking something that wasn't running....???
Anyway, checkdb steps are now running and going ahead.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply