December 14, 2010 at 11:12 am
To start with, I am embarrassed to post this, as I know that the best solution for any DB corruption errors is to restore from a backup, but because of a bad situation, this corruption was not found until the backups we have are too new they already have the corruption in them).
I have a situation where a DB is showing consistency errors:
"CHECKDB found 0 allocation errors and 122 consistency errors in database 'myDB'."
When I try to run any of the DBCC CHECKDB commands, I get errors. When I finally resorted to running "DBCC CHECKDB (myDB,REPAIR_ALLOW_DATA_LOSS)" it will run and complete with errors, but it will not fix the errors shown, I have run it about 15 times and the 122 consistency errors stay the same. Are there any SQL Server tricks to resolving the corruption? Obviously at this point, I am ok with some data loss, as we have some means of recovering the data manually.
I know that there are tools to supposedly recovering from corruption, but I don't trust any of the companies on name alone, can anyone speak to their reliability?
Thanks everyone.
December 14, 2010 at 12:31 pm
Please run the following and post the full and complete output.
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
CheckDB can't fix all errors. Any that it can't are irreparable and will require a restore from a clean backup or a complete recreate of the entire DB.
Take a look at this article. http://www.sqlservercentral.com/articles/65804/
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
December 14, 2010 at 12:55 pm
Thanks Gail, but we are out of luck with the backup. We run hourly TRAN logs, nightly DIFFs and weekly FULLs, but by the time this issue bubbled up, our oldest backups were two weeks later than when the corruption occurred and restoring from them retains the corruption.
Anyone know if any of those "SQL corruption restore" Tools work?
December 14, 2010 at 1:10 pm
Only ever tried one, didn't work.
Could you post the output of that command please?
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
December 14, 2010 at 1:18 pm
No Problem:
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
DBCC CHECKDB (<Database Name>) WITH TABLOCK,NO_INFOMSGS, ALL_ERRORMSGS
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -7782220156096217088 (type Unknown), page (1792:0). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 62916617 and -14.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 2314885530818445312 (type Unknown), page ID (3:36002) contains an incorrect page ID in its page header. The PageId in the page header = (8224:538976288).
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 4207603701783527424 (type Unknown), page (3:36000). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 62916617 and -1.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7307212932861984768 (type Unknown), page (3:35328). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 62916617 and -10.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7434917629258432512 (type Unknown), page (13602:1031039079). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 62916617 and -4.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7598813316113039360 (type Unknown), page (28265:1835356704). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 62916617 and -1.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7882818273307000832 (type Unknown), page (26983:779248995). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 62916617 and -10.
CHECKDB found 0 allocation errors and 7 consistency errors not associated with any single object.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:32991), slot 1, text ID 47895216128 is not referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:32991), slot 3, text ID 48042737664 is not referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:33001), slot 2, text ID 48653926400 is not referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:33006), slot 3, text ID 48963518464 is not referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:33032), slot 1, text ID 49579884544 is not referenced.
Msg 8928, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data): Page (3:35328) could not be processed. See other errors for details.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35328), slot 0, text ID 13390970880 is referenced by page (3:35319), slot 4, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35328), slot 1, text ID 48653926400 is referenced by page (3:206990), slot 7, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35328), slot 2, text ID 49579884544 is referenced by page (3:207108), slot 19, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35328), slot 3, text ID 49582702592 is referenced by page (3:207109), slot 4, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35328), slot 5, text ID 49582768128 is referenced by page (3:207109), slot 4, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35328), slot 6, text ID 49582833664 is referenced by page (3:207109), slot 4, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35328), slot 8, text ID 49582899200 is referenced by page (3:207109), slot 4, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35328), slot 10, text ID 49583226880 is referenced by page (3:207109), slot 6, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35328), slot 11, text ID 49583292416 is referenced by page (3:207109), slot 6, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data): Page (3:35329) could not be processed. See other errors for details.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35329), slot 0, text ID 13391167488 is referenced by page (3:35322), slot 4, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35329), slot 1, text ID 13395099648 is referenced by page (3:203733), slot 6, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35329), slot 2, text ID 13395165184 is referenced by page (3:203733), slot 6, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35329), slot 3, text ID 13395230720 is referenced by page (3:203733), slot 6, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35329), slot 4, text ID 13395296256 is referenced by page (3:203733), slot 6, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35329), slot 5, text ID 47895216128 is referenced by page (3:206911), slot 6, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35329), slot 6, text ID 48042737664 is referenced by page (3:206929), slot 14, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35329), slot 7, text ID 48963518464 is referenced by page (3:207030), slot 1, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35329), slot 8, text ID 49130897408 is referenced by page (3:207051), slot 10, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35329), slot 9, text ID 49130962944 is referenced by page (3:207051), slot 10, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35329), slot 10, text ID 49131028480 is referenced by page (3:207051), slot 10, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35329), slot 12, text ID 49131094016 is referenced by page (3:207051), slot 10, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data): Page (3:35330) could not be processed. See other errors for details.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35330), slot 0, text ID 13391364096 is referenced by page (3:35303), slot 4, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35330), slot 1, text ID 49131159552 is referenced by page (3:207051), slot 11, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35330), slot 2, text ID 49131225088 is referenced by page (3:207051), slot 11, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35330), slot 3, text ID 49131290624 is referenced by page (3:207051), slot 11, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35330), slot 5, text ID 49131356160 is referenced by page (3:207051), slot 11, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35330), slot 6, text ID 49132470272 is referenced by page (3:207051), slot 16, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35330), slot 7, text ID 49132535808 is referenced by page (3:207051), slot 16, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35330), slot 8, text ID 49132601344 is referenced by page (3:207051), slot 16, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35330), slot 10, text ID 49132666880 is referenced by page (3:207051), slot 16, but was not seen in the scan.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35384), slot 0, text ID 13395099648 is not referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35385), slot 0, text ID 13395099648 is not referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35386), slot 0, text ID 13395099648 is not referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:35387), slot 0, text ID 13395296256 is not referenced.
Msg 8928, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data): Page (3:36000) could not be processed. See other errors for details.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36000), slot 0, text ID 13493600256 is referenced by page (3:35934), slot 7, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data): Page (3:36001) could not be processed. See other errors for details.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36001), slot 0, text ID 13493600256 is referenced by page (3:35934), slot 7, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36001), slot 1, text ID 13493796864 is referenced by page (3:35934), slot 10, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data): Page (3:36002) could not be processed. See other errors for details.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36002), slot 0, text ID 13493862400 is referenced by page (3:35930), slot 17, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36002), slot 1, text ID 13494714368 is referenced by page (3:35987), slot 3, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36002), slot 2, text ID 43876876288 is referenced by page (3:206458), slot 19, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36002), slot 3, text ID 43876941824 is referenced by page (3:206458), slot 19, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36002), slot 4, text ID 43877007360 is referenced by page (3:206458), slot 19, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36002), slot 5, text ID 43877072896 is referenced by page (3:206458), slot 19, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36002), slot 6, text ID 43994578944 is referenced by page (3:206470), slot 0, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36002), slot 7, text ID 43994644480 is referenced by page (3:206470), slot 0, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36002), slot 8, text ID 43994710016 is referenced by page (3:206470), slot 0, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36002), slot 10, text ID 43994775552 is referenced by page (3:206470), slot 0, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36002), slot 12, text ID 44430000128 is referenced by page (3:206521), slot 25, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36002), slot 13, text ID 44430065664 is referenced by page (3:206521), slot 25, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36002), slot 14, text ID 44430131200 is referenced by page (3:206521), slot 25, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36002), slot 15, text ID 44430196736 is referenced by page (3:206521), slot 25, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data): Page (3:36003) could not be processed. See other errors for details.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36003), slot 1, text ID 13494059008 is referenced by page (3:203742), slot 11, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36003), slot 3, text ID 13494124544 is referenced by page (3:203742), slot 12, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36003), slot 4, text ID 13494190080 is referenced by page (3:203742), slot 12, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36003), slot 5, text ID 13494255616 is referenced by page (3:203742), slot 12, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36003), slot 6, text ID 13494321152 is referenced by page (3:203742), slot 12, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36003), slot 7, text ID 13496745984 is referenced by page (3:203742), slot 22, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36003), slot 9, text ID 13496811520 is referenced by page (3:203742), slot 22, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36003), slot 10, text ID 13496877056 is referenced by page (3:203742), slot 22, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36003), slot 11, text ID 13496942592 is referenced by page (3:203742), slot 22, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36003), slot 12, text ID 13497139200 is referenced by page (3:203742), slot 23, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36003), slot 13, text ID 13497204736 is referenced by page (3:203742), slot 23, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36003), slot 14, text ID 13497270272 is referenced by page (3:203742), slot 23, but was not seen in the scan.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36004), slot 0, text ID 13494321152 is not referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36020), slot 1, text ID 13497139200 is not referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36021), slot 1, text ID 13496942592 is not referenced.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36221), slot 1, text ID 13538295808 is referenced by page (3:36194), slot 5, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36222), slot 1, text ID 13539082240 is referenced by page (3:36212), slot 10, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36223), slot 0, text ID 13538361344 is referenced by page (3:36194), slot 6, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:36671), slot 0, text ID 13613531136 is referenced by page (3:36645), slot 6, but was not seen in the scan.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:160419), slot 1, text ID 43877072896 is not referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:160422), slot 0, text ID 43876876288 is not referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:160933), slot 0, text ID 43994578944 is not referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:178998), slot 0, text ID 49130897408 is not referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:178999), slot 0, text ID 49131159552 is not referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:179006), slot 0, text ID 49132470272 is not referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 71864337322213376 (type LOB data). The off-row data node at page (3:181018), slot 0, text ID 49582702592 is not referenced.
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 13390970880 owned by data record identified by RID = (3:203732:21)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 13391167488 owned by data record identified by RID = (3:203732:22)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 13391364096 owned by data record identified by RID = (3:203732:23)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 13395099648 owned by data record identified by RID = (3:203733:6)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 13493600256 owned by data record identified by RID = (3:203742:10)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 13493862400 owned by data record identified by RID = (3:203742:11)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 13494124544 owned by data record identified by RID = (3:203742:12)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 13494714368 owned by data record identified by RID = (3:203742:14)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 13496745984 owned by data record identified by RID = (3:203742:22)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 13497139200 owned by data record identified by RID = (3:203742:23)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 13538295808 owned by data record identified by RID = (3:203747:14)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 13539082240 owned by data record identified by RID = (3:203747:16)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 13613531136 owned by data record identified by RID = (3:203754:21)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 43876941824 owned by data record identified by RID = (3:206458:19)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 43994644480 owned by data record identified by RID = (3:206470:0)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 44430065664 owned by data record identified by RID = (3:206521:25)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 47895216128 owned by data record identified by RID = (3:206911:6)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 48042737664 owned by data record identified by RID = (3:206929:14)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 48653926400 owned by data record identified by RID = (3:206990:7)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 48963518464 owned by data record identified by RID = (3:207030:1)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 49130962944 owned by data record identified by RID = (3:207051:10)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 49131225088 owned by data record identified by RID = (3:207051:11)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 49132535808 owned by data record identified by RID = (3:207051:16)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 49579884544 owned by data record identified by RID = (3:207108:19)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 49582768128 owned by data record identified by RID = (3:207109:4)
Msg 8929, Level 16, State 1, Line 1
Object ID 1346103836, index ID 1, partition ID 72057594496090112, alloc unit ID 72057594497138688 (type In-row data): Errors found in off-row data with ID 49583292416 owned by data record identified by RID = (3:207109:6)
CHECKDB found 0 allocation errors and 115 consistency errors in table 'XXXX.BH_UserMessage' (object ID 1346103836).
CHECKDB found 0 allocation errors and 122 consistency errors in database 'XXXXX'.
December 14, 2010 at 1:33 pm
There's no minimum repair level specified, hence we can conclude it's not considered repairable.
Since you have no backups, you're pretty much left with the other option I mentioned - recreate the DB. Script out what objects you can. Export what data you can and recreate the DB from that.
I suggest, once you have things fixed, put a checkDB task before doing a backup. There's no point in backing up a corrupt DB so you generally want to know about the damage before starting the backup.
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
December 14, 2010 at 1:37 pm
Thanks Gail, that is what I feared. I have already started writing some BCP scripts to pull the data out into a new DB shell. We have weekly CHECKDB jobs that would generally catch these, but we encountered a situation with this one that allowed it to slip through.
Thanks for the help.
December 14, 2010 at 7:55 pm
Is there any possibility of getting copies of a backup prior to the corruption? Normally, you would be sending your backups offsite somewhere for DR.
If so, you could restore from that backup and apply all transaction log backups from that point forward. Again, this is if you have all of those backups available.
If not, I would recommend that not only do you perform an integrity check before backing up the database (and, if errors found pull the fire alarms), but - you need to put in a process to copy those backup files offsite.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 15, 2010 at 10:21 am
Unfortunately, backups (both onsite and offsite) have the corruption.
Yes, we have a process of moving older backups offsite, but because of the large number of databases that we manage (greater than 3,000 DBs) we need to discard backups after a certain amount of time for storage reasons.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply