Data Corruption... SQL 2000 SP4 Help!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Data Corruption... SQL 2000 SP4 Help!

Author

Topic

CityOfLynden
Starting Member

6 Posts

Posted - 2010-02-04 : 21:53:13

OK, so we had some RAID controller memory go bad on us, and the server crashed. Thankfully, it crashed hard enough that it was fully down so any data corruption shouldn't be too horrendous. We replaced the RAM, ran checkdsk, and got the server back up. No errors in the event logs or SQL logs, and users started entering data again. However, looking at the event logs since then, we can see the nightly maintenance plan has been failing, and now we saw today the first error.

Need to know what we can do to recover from this, hopefully without data loss. I have backups from before this, but if we have to go back a week on data entry, it's going to really suck big time. Anyone have expertise on what this is telling me, and thoughts on what to do about it? Thanks!

Here's the results of a: DBCC CheckDB (Agresso, REPAIR_REBUILD) WITH NO_INFOMSGS, ALL_ERRORMSGS

Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 335417376768 owned by data record identified by RID = (1:7381:4) id = 604760757 and indid = 2.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 335417442304 owned by data record identified by RID = (1:7381:5) id = 604760757 and indid = 3.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 335417507840 owned by data record identified by RID = (1:7381:6) id = 604760757 and indid = 4.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 335417573376 owned by data record identified by RID = (1:7381:7) id = 604760757 and indid = 5.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 335417638912 owned by data record identified by RID = (1:7381:8) id = 604760757 and indid = 6.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 335422488576 owned by data record identified by RID = (1:7381:9) id = 605801857 and indid = 1.
The repair level on the DBCC statement caused this repair to be bypassed.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 336451796992 owned by data record identified by RID = (1:14247:0) id = 1476692154 and indid = 1.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 335436251136 owned by data record identified by RID = (1:22756:1) id = 1231499616 and indid = 2.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 335436316672 owned by data record identified by RID = (1:22756:2) id = 1231499616 and indid = 3.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 335436382208 owned by data record identified by RID = (1:22756:3) id = 1231499616 and indid = 4.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 335442477056 owned by data record identified by RID = (1:22756:4) id = 1231743932 and indid = 1.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 335442542592 owned by data record identified by RID = (1:22756:5) id = 1231743932 and indid = 2.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 335425961984 owned by data record identified by RID = (1:22756:9) id = 1237886422 and indid = 2.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 335423930368 owned by data record identified by RID = (1:22756:12) id = 1240465822 and indid = 1.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 336429318144 owned by data record identified by RID = (1:23797:4) id = 709733731 and indid = 5.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 336429383680 owned by data record identified by RID = (1:23797:5) id = 709733731 and indid = 6.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 335446999040 owned by data record identified by RID = (1:24679:1) id = 1282259773 and indid = 2.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 335447064576 owned by data record identified by RID = (1:24679:2) id = 1282259773 and indid = 3.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 335447130112 owned by data record identified by RID = (1:24679:3) id = 1282259773 and indid = 4.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 335447195648 owned by data record identified by RID = (1:24679:4) id = 1282259773 and indid = 5.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 335421964288 owned by data record identified by RID = (1:25705:1) id = 1300756328 and indid = 1.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 336400416768 owned by data record identified by RID = (1:35929:0) id = 2040468672 and indid = 1.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 336401137664 owned by data record identified by RID = (1:36845:4) id = 722305549 and indid = 2.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 336394059776 owned by data record identified by RID = (1:106031:1) id = 2117150183 and indid = 2.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 336394125312 owned by data record identified by RID = (1:106031:2) id = 2117150183 and indid = 3.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 336920248320 owned by data record identified by RID = (1:115368:2) id = 1976638426 and indid = 1.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 335424651264 owned by data record identified by RID = (1:195802:1) id = 2053786290 and indid = 2.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 335426093056 owned by data record identified by RID = (1:195802:3) id = 2053889329 and indid = 2.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:1280), slot 1, text ID 336393797632 is not referenced.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:1284), slot 5, text ID 336398843904 does not match its reference from page (1:7381), slot 9.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:8392), slot 18, text ID 335442477056 is referenced by page (1:22756), slot 4, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:8392), slot 19, text ID 335442542592 is referenced by page (1:22756), slot 5, but was not seen in the scan.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:27109), slot 1, text ID 336393928704 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:27110), slot 1, text ID 336393994240 is not referenced.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:39359), slot 15, text ID 336393732096 is not referenced.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:51707), slot 18, text ID 336412606464 does not match its reference from page (1:22756), slot 1.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:51707), slot 20, text ID 336412672000 does not match its reference from page (1:22756), slot 2.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:51707), slot 22, text ID 336412737536 does not match its reference from page (1:22756), slot 3.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:51734), slot 11, text ID 336402317312 does not match its reference from page (1:22756), slot 9.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:51734), slot 15, text ID 336402448384 does not match its reference from page (1:195802), slot 3.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:53765), slot 1, text ID 336423354368 does not match its reference from page (1:24679), slot 1.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:53765), slot 3, text ID 336423419904 does not match its reference from page (1:24679), slot 2.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:53765), slot 4, text ID 336429318144 does not match its reference from page (1:24679), slot 3.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:53765), slot 4, text ID 336429318144 does not match its reference from page (1:24679), slot 3.
Server: Msg 8974, Level 16, State 1, Line 1
Text node referenced by more than one node. Object ID 2, text, ntext, or image node page (1:53765), slot 4, text ID 336429318144 is pointed to by page (1:53765), slot 5 and by page (1:24679), slot 3.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:53765), slot 6, text ID 336429383680 does not match its reference from page (1:24679), slot 4.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:53765), slot 6, text ID 336429383680 does not match its reference from page (1:24679), slot 4.
Server: Msg 8974, Level 16, State 1, Line 1
Text node referenced by more than one node. Object ID 2, text, ntext, or image node page (1:53765), slot 6, text ID 336429383680 is pointed to by page (1:53765), slot 7 and by page (1:24679), slot 4.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:55341), slot 12, text ID 335417376768 is referenced by page (1:7381), slot 4, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:55341), slot 14, text ID 335417442304 is referenced by page (1:7381), slot 5, but was not seen in the scan.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:66482), slot 6, text ID 336393863168 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:66654), slot 2, text ID 336451796992 is not referenced.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:69030), slot 0, text ID 336920248320 does not match its reference from page (1:14247), slot 0.
Server: Msg 8974, Level 16, State 1, Line 1
Text node referenced by more than one node. Object ID 2, text, ntext, or image node page (1:69030), slot 0, text ID 336920248320 is pointed to by page (1:14247), slot 0 and by page (1:115368), slot 2.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:76367), slot 9, text ID 336401006592 is not referenced.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:76367), slot 12, text ID 336401137664 does not match its reference from page (1:195802), slot 1.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:76367), slot 12, text ID 336401137664 does not match its reference from page (1:195802), slot 1.
Server: Msg 8974, Level 16, State 1, Line 1
Text node referenced by more than one node. Object ID 2, text, ntext, or image node page (1:76367), slot 12, text ID 336401137664 is pointed to by page (1:76367), slot 13 and by page (1:195802), slot 1.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:85145), slot 22, text ID 336418832384 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:85145), slot 23, text ID 336418897920 is not referenced.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:117643), slot 1, text ID 336394059776 does not match its reference from page (1:7381), slot 6.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:117643), slot 1, text ID 336394059776 does not match its reference from page (1:7381), slot 6.
Server: Msg 8974, Level 16, State 1, Line 1
Text node referenced by more than one node. Object ID 2, text, ntext, or image node page (1:117643), slot 1, text ID 336394059776 is pointed to by page (1:117643), slot 5 and by page (1:7381), slot 6.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:117643), slot 5, text ID 336394059776 does not match its reference from page (1:7381), slot 7.
Server: Msg 8974, Level 16, State 1, Line 1
Text node referenced by more than one node. Object ID 2, text, ntext, or image node page (1:117643), slot 5, text ID 336394059776 is pointed to by page (1:106031), slot 1 and by page (1:7381), slot 7.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:117643), slot 6, text ID 336394125312 does not match its reference from page (1:7381), slot 8.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:117643), slot 6, text ID 336394125312 does not match its reference from page (1:7381), slot 8.
Server: Msg 8974, Level 16, State 1, Line 1
Text node referenced by more than one node. Object ID 2, text, ntext, or image node page (1:117643), slot 6, text ID 336394125312 is pointed to by page (1:117643), slot 7 and by page (1:7381), slot 8.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:226220), slot 5, text ID 336398319616 does not match its reference from page (1:25705), slot 1.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:272498), slot 8, text ID 336400285696 is not referenced.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:281765), slot 2, text ID 336423485440 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:281765), slot 4, text ID 336423550976 is not referenced.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:290138), slot 1, text ID 336400416768 does not match its reference from page (1:22756), slot 12.
Server: Msg 8974, Level 16, State 1, Line 1
Text node referenced by more than one node. Object ID 2, text, ntext, or image node page (1:290138), slot 1, text ID 336400416768 is pointed to by page (1:35929), slot 0 and by page (1:22756), slot 12.
Server: Msg 8959, Level 16, State 1, Line 1
Table error: IAM page (1:17078) for object ID 165455559, index ID 2 is linked in the IAM chain for object ID 604760757, index ID 2 by page (0:0).
Server: Msg 8959, Level 16, State 1, Line 1
Table error: IAM page (1:17078) for object ID 165455559, index ID 2 is linked in the IAM chain for object ID 604760757, index ID 2 by page (0:0).
Server: Msg 8959, Level 16, State 1, Line 1
Table error: IAM page (1:16988) for object ID 306304067, index ID 2 is linked in the IAM chain for object ID 2053786290, index ID 2 by page (0:0).
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 0 allocation errors and 74 consistency errors in table 'sysindexes' (object ID 2).
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'aaglynden1_200001' (object ID 165455559).
Server: Msg 8959, Level 16, State 1, Line 1
Table error: IAM page (1:16988) for object ID 306304067, index ID 2 is linked in the IAM chain for object ID 2053786290, index ID 2 by page (0:0).
Server: Msg 2576, Level 16, State 1, Line 1
IAM page (0:0) is pointed to by the previous pointer of IAM page (1:15574) object ID 604760757 index ID 2 but was not detected in the scan.
Server: Msg 7965, Level 16, State 1, Line 1
Table error: Could not check object ID 604760757, index ID 2 due to invalid allocation (IAM) page(s).
Server: Msg 2576, Level 16, State 1, Line 1
IAM page (0:0) is pointed to by the previous pointer of IAM page (1:13932) object ID 605801857 index ID 0 but was not detected in the scan.
Server: Msg 7965, Level 16, State 1, Line 1
Table error: Could not check object ID 605801857, index ID 1 due to invalid allocation (IAM) page(s).
Server: Msg 8959, Level 16, State 1, Line 1
Table error: IAM page (1:17724) for object ID 962258633, index ID 2 is linked in the IAM chain for object ID 1282259773, index ID 2 by page (0:0).
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'aaglynden1_200506' (object ID 306304067).
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 1 allocation errors and 1 consistency errors in table 'aaglynden1_199912' (object ID 604760757).
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 1 allocation errors and 1 consistency errors in table 'aaglynden1_200212' (object ID 605801857).
The repair level on the DBCC statement caused this repair to be bypassed.
Server: Msg 8959, Level 16, State 1, Line 1
Table error: IAM page (1:17724) for object ID 962258633, index ID 2 is linked in the IAM chain for object ID 1282259773, index ID 2 by page (0:0).
Server: Msg 8959, Level 16, State 1, Line 1
Table error: IAM page (1:13935) for object ID 1005803282, index ID 1 is linked in the IAM chain for object ID 605801857, index ID 1 by page (0:0).
Server: Msg 8959, Level 16, State 1, Line 1
Table error: IAM page (1:13935) for object ID 1005803282, index ID 1 is linked in the IAM chain for object ID 605801857, index ID 1 by page (0:0).
Server: Msg 2576, Level 16, State 1, Line 1
IAM page (0:0) is pointed to by the previous pointer of IAM page (1:17630) object ID 1231499616 index ID 2 but was not detected in the scan.
Server: Msg 7965, Level 16, State 1, Line 1
Table error: Could not check object ID 1231499616, index ID 2 due to invalid allocation (IAM) page(s).
Server: Msg 2576, Level 16, State 1, Line 1
IAM page (0:0) is pointed to by the previous pointer of IAM page (1:14102) object ID 1237886422 index ID 2 but was not detected in the scan.
Server: Msg 7965, Level 16, State 1, Line 1
Table error: Could not check object ID 1237886422, index ID 2 due to invalid allocation (IAM) page(s).
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'acrcurrcodes' (object ID 962258633).
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'aaglynden1_200213' (object ID 1005803282).
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 1 allocation errors and 1 consistency errors in table 'acbbankbal' (object ID 1231499616).
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
Server: Msg 2576, Level 16, State 1, Line 1
IAM page (0:0) is pointed to by the previous pointer of IAM page (1:16941) object ID 1240465822 index ID 0 but was not detected in the scan.
Server: Msg 7965, Level 16, State 1, Line 1
Table error: Could not check object ID 1240465822, index ID 1 due to invalid allocation (IAM) page(s).
Server: Msg 2576, Level 16, State 1, Line 1
IAM page (0:0) is pointed to by the previous pointer of IAM page (1:17716) object ID 1282259773 index ID 2 but was not detected in the scan.
Server: Msg 7965, Level 16, State 1, Line 1
Table error: Could not check object ID 1282259773, index ID 2 due to invalid allocation (IAM) page(s).
Server: Msg 2576, Level 16, State 1, Line 1
IAM page (0:0) is pointed to by the previous pointer of IAM page (1:16755) object ID 1300756328 index ID 0 but was not detected in the scan.
Server: Msg 7965, Level 16, State 1, Line 1
Table error: Could not check object ID 1300756328, index ID 1 due to invalid allocation (IAM) page(s).
CHECKDB found 1 allocation errors and 1 consistency errors in table 'aaglynden1_200711' (object ID 1237886422).
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 1 allocation errors and 1 consistency errors in table 'aaglynden1_200407' (object ID 1240465822).
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 1 allocation errors and 1 consistency errors in table 'acrcomment' (object ID 1282259773).
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 1 allocation errors and 1 consistency errors in table 'aaglynden1_200204' (object ID 1300756328).
Server: Msg 8959, Level 16, State 1, Line 1
Table error: IAM page (1:14103) for object ID 1491895427, index ID 2 is linked in the IAM chain for object ID 1237886422, index ID 2 by page (0:0).
Server: Msg 8959, Level 16, State 1, Line 1
Table error: IAM page (1:14103) for object ID 1491895427, index ID 2 is linked in the IAM chain for object ID 1237886422, index ID 2 by page (0:0).
Server: Msg 8959, Level 16, State 1, Line 1
Table error: IAM page (1:17631) for object ID 1810977678, index ID 3 is linked in the IAM chain for object ID 1231499616, index ID 2 by page (0:0).
Server: Msg 8959, Level 16, State 1, Line 1
Table error: IAM page (1:17631) for object ID 1810977678, index ID 3 is linked in the IAM chain for object ID 1231499616, index ID 2 by page (0:0).
Server: Msg 8959, Level 16, State 1, Line 1
Table error: IAM page (1:16945) for object ID 2040468672, index ID 1 is linked in the IAM chain for object ID 1240465822, index ID 1 by page (0:0).
Server: Msg 8959, Level 16, State 1, Line 1
Table error: IAM page (1:16945) for object ID 2040468672, index ID 1 is linked in the IAM chain for object ID 1240465822, index ID 1 by page (0:0).
Server: Msg 2576, Level 16, State 1, Line 1
IAM page (0:0) is pointed to by the previous pointer of IAM page (1:16984) object ID 2053786290 index ID 2 but was not detected in the scan.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'aaglynden1_200712' (object ID 1491895427).
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'acbbankrec' (object ID 1810977678).
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'aaglynden1_200409' (object ID 2040468672).
Server: Msg 7965, Level 16, State 2, Line 1
Table error: Could not check object ID 2053786290, index ID 2 due to invalid allocation (IAM) page(s).
Server: Msg 2576, Level 16, State 1, Line 1
IAM page (0:0) is pointed to by the previous pointer of IAM page (1:14111) object ID 2053889329 index ID 2 but was not detected in the scan.
Server: Msg 2575, Level 16, State 1, Line 1
IAM page (1:10691) is pointed to by the next pointer of IAM page (0:0) object ID 2053889329 index ID 2 but was not detected in the scan.
Server: Msg 7965, Level 16, State 1, Line 1
Table error: Could not check object ID 2053889329, index ID 2 due to invalid allocation (IAM) page(s).
Server: Msg 8946, Level 16, State 1, Line 1
Table error: Allocation page (1:10691) has invalid IAM_PAGE page header values. Type is 1. Check type, object ID and page ID on the page.
Server: Msg 8946, Level 16, State 1, Line 1
Table error: Allocation page (1:10691) has invalid IAM_PAGE page header values. Type is 1. Check type, object ID and page ID on the page.
Server: Msg 8959, Level 16, State 1, Line 1
Table error: IAM page (1:6741) for object ID 2100759178, index ID 1 is linked in the IAM chain for object ID 1300756328, index ID 1 by page (0:0).
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 1 allocation errors and 1 consistency errors in table 'aaglynden1_200505' (object ID 2053786290).
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 2 allocation errors and 3 consistency errors in table 'aaglynden1_200713' (object ID 2053889329).
The repair level on the DBCC statement caused this repair to be bypassed.
Server: Msg 8959, Level 16, State 1, Line 1
Table error: IAM page (1:6741) for object ID 2100759178, index ID 1 is linked in the IAM chain for object ID 1300756328, index ID 1 by page (0:0).
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'aaglynden1_200205' (object ID 2100759178).
CHECKDB found 10 allocation errors and 101 consistency errors in database 'Agresso'.

CityOfLynden
Starting Member

6 Posts

Posted - 2010-02-04 : 22:55:41

Also, if I need to restore, I have a full backup from the night before the corruption. If I restore it and it's transaction log, then is it possible or advisable to restore the newer transaction log since then? If the database is corrupt, is the transaction log also automatically corrupt? If it is not, that would be one avenue of recovery.

Is there a way to check the transaction log for errors? Thanks!

vmenon
Starting Member

17 Posts

Posted - 2010-02-05 : 02:06:12

Message "Errors found in text ID 335417573376 owned by data record identified by RID" indicate corruption in a text node) when the owner of the corrupt text node can be found.

Please can you try out the below plan.

Full backup the database from productio.
Restore the database onto another SQL instance node.
Drop all indexes inclusive of clustered, non-clusterd and full text. Before you start dropping, You will need to script out indexes, relationships etc, which in itself is a real big task on production databases.
Execute DBCC CHECKDB with REPAIR_REBUILD, hopefully this should resolve IAM related errors.

Once DBCC CHECKDB returns a clean all-clear message, rebuild all dropped clustered, non-clustere indexes, full text, relanship etc. Test application against this database.

If this works, replicate the steps on production during non-peak hours indeally by advertising a window down-time to stake holders.

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 02:53:48

"and users started entering data again"

:O

Change DB to DBO Only, single user - or READ-ONLY, to stop anyone connecting

Take a TLog backup

Create a new (temporary) database (assuming you have disk space, otherwise use a different machine)

Restore last FULL that you have and all TLog backups since (including the "final" one I listed above)

Run


DBCC CheckDB (NewTempDBName) WITH NO_INFOMSGS, ALL_ERRORMSGS

if it is clean take a full backup, restore to your normal DB, and drop the NewTempDB

If it is NOT clean that:

Restore just the full backup to NewTempDBName run DBCC CheckDB on it. Is that clean? if not go back to an earlier Full Backup.

Once you have a clean full backup re-restore that Full backup and all Tlogs since and use DBCC CheckDB to see if that is clean.

You can, obviously, stop at an earlier Tlog backup if the last one/few are damaged

(Not sure, but you may be able to run DBCC CHECKDB if you use STANDBY during the restores - i.e. to save having to do all the restores, discover there is damage, and then have to repeat "in blocks" to find where the damage occurred)

Next time there is a hardware fault perform DBCC CheckDB before anyone resumes working.

Upgrade to SQL 2005 / SQL 2008 and turn on Torn Page Detection or better still Checksum so that any errors come to light more quickly.

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 02:57:13

Sorry, meant to answer your questions:

"If the database is corrupt, is the transaction log also automatically corrupt?"

I think there is a good chance that the TLog backups will be clean. I have recovered corrupted databases from last Full Backup and all the TLog backups thereafter, and got a clean database as a result. The Tlog is not storing the database pages (that are corrupted), its different type of information, hence there is a chance

. However, if the controller breaks the chances are all disk files created thereafter, or copied, etc. may be corrupted too.

"Is there a way to check the transaction log for errors?"

I don't think so, Restore and use DBCC CHECKDB is the only way I know. (If the file fails to restore then it is corrupted, but if it restored OK there is no telling if the data it contains is good, or bad, other than to restore it.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-02-05 : 05:58:07

Run the following, post the full results


DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

If you can, rather restore from backup, repairing will cause data loss in this case. (lots of data loss) I also think you have some irreparable errors, but can't be 100% sure of that.

It's highly unlikely that the corruption will be in the transaction logs (though it's possible for the log backups themselves to be corrupt)

--
Gail Shaw
SQL Server MVP

CityOfLynden
Starting Member

6 Posts

Posted - 2010-02-05 : 12:48:52

Thanks for all the suggestions everyone! I am working on restoring from a known good backup, then trying to restore transaction logs as far forward as I can. I have one more database that is a problem, tell me if you think it is recoverable without going to the backups, thanks!

Results of a DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS on this database:

Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 247364911104 owned by data record identified by RID = (1:27112:1) id = 1118627028 and indid = 2.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 247364976640 owned by data record identified by RID = (1:27112:2) id = 1118627028 and indid = 3.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 248346771456 owned by data record identified by RID = (1:41315:11) id = 1714105147 and indid = 5.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 0, text ID 248346771456 does not match its reference from page (1:4162), slot 5.
Server: Msg 8974, Level 16, State 1, Line 1
Text node referenced by more than one node. Object ID 2, text, ntext, or image node page (1:4162), slot 0, text ID 248346771456 is pointed to by page (1:7452), slot 0 and by page (1:4162), slot 5.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 2, text ID 247208804352 does not match its reference from page (1:27112), slot 1.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 4, text ID 247208869888 does not match its reference from page (1:27112), slot 2.
The repair level on the DBCC statement caused this repair to be bypassed.
The system cannot self repair this error.
The system cannot self repair this error.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 5, text ID 247169744896 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 6, text ID 247169810432 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 7, text ID 247169875968 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 8, text ID 247169941504 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 9, text ID 247170007040 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 10, text ID 247170072576 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 11, text ID 247170138112 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 12, text ID 247170203648 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 13, text ID 247170269184 is not referenced.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 14, text ID 247170334720 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 15, text ID 247170400256 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 17, text ID 247170465792 is not referenced.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 0 allocation errors and 19 consistency errors in table 'sysindexes' (object ID 2).
CHECKDB found 0 allocation errors and 19 consistency errors in database 'Hansen'.

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 12:54:44

That doesn't look like a plain DBCC CHECKDB command, it looks like a REPAIR was run. If that's the case what REPAIR command did you use?

The folk who are able to interpret these reports will want the output from just a plain

DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-02-05 : 14:39:54

Please run checkDB without a repair level and post the oputput. I need the diagnostics of what's wrong, not the output of an attempt to repair.

--
Gail Shaw
SQL Server MVP

CityOfLynden
Starting Member

6 Posts

Posted - 2010-02-05 : 16:50:27

Here it is without the repair option, just the checkdb:

Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 247364911104 owned by data record identified by RID = (1:27112:1) id = 1118627028 and indid = 2.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 247364976640 owned by data record identified by RID = (1:27112:2) id = 1118627028 and indid = 3.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 248346771456 owned by data record identified by RID = (1:41315:11) id = 1714105147 and indid = 5.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 0, text ID 248346771456 does not match its reference from page (1:4162), slot 5.
Server: Msg 8974, Level 16, State 1, Line 1
Text node referenced by more than one node. Object ID 2, text, ntext, or image node page (1:4162), slot 0, text ID 248346771456 is pointed to by page (1:7452), slot 0 and by page (1:4162), slot 5.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 2, text ID 247208804352 does not match its reference from page (1:27112), slot 1.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 4, text ID 247208869888 does not match its reference from page (1:27112), slot 2.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 5, text ID 247169744896 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 6, text ID 247169810432 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 7, text ID 247169875968 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 8, text ID 247169941504 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 9, text ID 247170007040 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 10, text ID 247170072576 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 11, text ID 247170138112 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 12, text ID 247170203648 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 13, text ID 247170269184 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 14, text ID 247170334720 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 15, text ID 247170400256 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:4162), slot 17, text ID 247170465792 is not referenced.
CHECKDB found 0 allocation errors and 19 consistency errors in table 'sysindexes' (object ID 2).
CHECKDB found 0 allocation errors and 19 consistency errors in database 'Hansen'.

Kristen
Test

22859 Posts

Posted - 2010-02-06 : 01:46:44

Gail will know the answer, but may not be around over the weekend?

If you are working on this I would recommend my earlier suggestion:

Lock all users out of the database (or tell them any new work will be lost)
Take a final Tlog backup
Restore last Full Backup to a new, temporary, database and all subsequent Tlog backups, including "Final" Tlog backup
Run DBCC CHECKDB on the temporary database

If it is clean you are good-to-go. Back it up and restore to the Live database (or re-perform the restore steps you did earlier)

P.S. In Enterprise Manager right click MyDatabase and choose Properties; in OPTIONS check that "Torn Page Detection" is ticked. That will give you some early warning if you get a corruption in the database. (Better would be to upgrade to SQL 2005 / SQL 2008 and choose CHECKSUM)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-02-06 : 04:15:36

Restore from backup is the best option, checkDB cannot fix those (and besides, I don't know how much data you lost by running repair). If you have no backup, there may be a hack-fix that might work. Emphasis might. Restore from backup is best.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-02-06 : 04:27:06

And there was me thinking you might have taken the weekend off!!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-02-06 : 09:19:08

I'm a consultant and I work from home. Weekends, weekdays, doesn't make much difference any more. Currently finishing off some performance analysis. I spent most of yesterday (friday) afternoon playing Hellgate London. Wasn't in a working mood.

--
Gail Shaw
SQL Server MVP

CityOfLynden
Starting Member

6 Posts

Posted - 2010-02-08 : 11:39:31

Thanks for the help everyone. Sadly, all the recent backups were corrupt versions of the database to, as were the transaction logs. So even though there was less damage to this database than the 1st one I posted, we ended up having to do a lot of re-data entry to get back up and running. Thankfully, this database sees about 10 times less activity than the 1st one. On that first one I posted I was able to recover (via restoring the transaction log after a good backup from Jan 26) up through Feb 2. It was fun learning how to pick just through a specific date/time in the transaction log for recovery, since that transaction log had corruption on Feb 3.

As for switching to SQL 2005 or 2008, we run most of our databases on 2005 and 2008. Unfortunately, our vendor for our finance system is dragging their heels on migrating us to a new version of their software that runs on either 2005 or 2008. We were supposed to migrate over in November, but they haven't finished our payslips, checks, and some of our reports yet, se we can't migrate to the new version on our newer servers. Hopefully in March it will get done.

Will look into turning on torn page detection, as that would have caught the problem sooner. Thanks again!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-02-09 : 00:07:26

quote:
Originally posted by CityOfLynden

Will look into turning on torn page detection, as that would have caught the problem sooner.

No it wouldn't have. Torn page (and CheckSum on SQL 2005) are page verification techniques, SQL can use them to tell that a page is damaged, but only when the page is read. Torn page detection is enabled on all databases by default in SQL 2000. It's on unless someone goes and removes it.

What would have caught this problem sooner is regular scheduled CheckDB of all databases with someone monitoring the results

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 08:19:35

Application would have received an error, though, if TornPage or Checksum was turned on AND the application tried to retreive some data from a corrupted part of the file, wouldn't it?

Would have given early warning (assuming a duff part of the file was read ...)

Also (with CHECKSUM turned on) attempting to backup corrupted data would have failed - again, an "early warning"

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-02-09 : 11:23:48

quote:
Originally posted by Kristen

Application would have received an error, though, if TornPage or Checksum was turned on AND the application tried to retreive some data from a corrupted part of the file, wouldn't it?

Considering the severity and types of errors that the OP has, SQL would have thrown an error if it encountered them regardless. However you cannot depend on an application maybe reading a corrupt page to detect corruption early.

Checksum (and to a lesser extent torn page) will allow SQL to tell that there's something wrong with a page when it initially reads it. Depending on how severe the damage is, without either, errors will still be thrown if the page header or row headers are damaged

quote:
Also (with CHECKSUM turned on) attempting to backup corrupted data would have failed - again, an "early warning"

Only if backing up with the WITH CHECKSUM option afaik

The way to detect corruption 'early' is to run regular integrity checks on the database.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 11:32:51

"However you cannot depend on an application maybe reading a corrupt page to detect corruption early."

Indeed, no substitute for regular DBCC CHECKDB ... just a little extra insurance

"Only if backing up with the WITH CHECKSUM option afaik"

Ah, thanks for that, I'd better double check what we are doing at present then

CityOfLynden
Starting Member

6 Posts

Posted - 2010-02-09 : 11:48:49

Yeah, what we found out is that after the initial hardware failure, our email notification of any DBCC CheckDB errors was also corrupt, so we didn't receive the notices. A real bummer. We run DB maintenance every night to check for corruption, including a DBCC CheckDB, but rely on notification to get a heads up about any errors. We are a small I.T. staff (2) covering 7 departments across 11 buildings around the city, and often are not even in our own office all day, or in the server rooms. In this case, we were busy adding new mobile systems to some new police cars and emergency response vehicles for the next few days after fixing the hardware problem with the server. We checked the logs immediately after bringing the server back up, and had no errors. We asked the users to try out all their functions to be sure the data was solid, and everything checked out ok at the time.

What we failed to do was manually check the maintenance log results the next few nights. We thought we would receive email notification like we usually do if there's a problem. However, the corruption was unusual in that it was extensive enough to break sql mail, generate some errors in all the databases, including master, model, etc, and yet load up with no errors, and work fine for 7 more work days before anyone noticed a problem. We have definitely learned from this one. Any time we have a failure of this type, we are going to manually run dbcc checkdb ourselves for a few days afterwards to verify everything is healthy, not just rely on our mail notification of then nightly maintenance plan.

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 12:11:38

Its closing the stable door after the horse has bolted, but I work a positive-vetting process.

I have a Perl script that gets the RPT files from the housekeeping routines, and strips out all the "Normal" information that it expects to see (allowing for variable content, such as date, filesize, etc).

What it expects to be left with is a completely empty file; anything left behind is "abnormal behaviour".

It did take our Summer Intern quite a long time to build!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources