CHECKDB Errors Reported - SQL Server Forums

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

CHECKDB Errors Reported

Author

Topic

sureshot
Yak Posting Veteran

72 Posts

Posted - 2005-12-02 : 14:25:34

I actually ran this on a reporting copy of my DB (restored once a week). I would guess the errors are present in the main copy as well. I fear running the check during production hours as it takes 2.5 hours and the system (4 processor) is under a lot of load for almost the entire day and much of the night.

So I've got errors on the tables:

sessions ID(462624691)
map ID(510624862)
links ID(1790629422)

It looks like the repair with data loss is my only choice but I would definitely prefer to know which rows are messed up. I'm also wary of running the repair as it's probably going to take well over an hour on the huge sessions table. I don't know how it got in this corrupted state. I inherited a DB of this size (50GB) that's been known to be very sensitive to any admin or index fiddling due to the high load. After I get this repaired I feel I should run the CHECKDB in a maintenance plan but I'm not sure if this will cause slowdowns in itself if it runs for 2.5 hours. The DB backup takes over 2 hours leaving almost no spare time in the off peak time.

Any advice/suggestions? Thanks.

DBCC results for 'dbloc'.

DBCC results for 'sessions'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 462624691, index ID 0: Page (1:9517861) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 462624691, index ID 0: Page (1:9659261) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 462624691, index ID 7: Page (1:5825743) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 462624691, index ID 7: Page (1:5890660) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 462624691, index ID 7: Page (1:5931145) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 462624691, index ID 7: Page (1:5950021) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 462624691, index ID 7: Page (1:5978420) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 462624691, index ID 7: Page (1:5988261) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 462624691, index ID 7: Page (1:6046306) could not be processed. See other errors for details.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 109 and 110.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 112 and 113.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 113 and 114.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 114 and 115.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 117 and 118.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 118 and 119.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 121 and 122.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 122 and 123.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 124 and 125.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 510624862, index ID 0: Page (1:11858027) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 510624862, index ID 0, page (1:11858027). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 510624862, index ID 0: Page (1:11865547) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 510624862, index ID 0, page (1:11865547). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 510624862, index ID 2: Page (1:11889147) could not be processed. See other errors for details.
DBCC results for 'sessions'.
There are 153364096 rows in 3102681 pages for object 'sessions'.
CHECKDB found 0 allocation errors and 9 consistency errors in table 'sessions' (object ID 462624691).
DBCC results for 'map'.
Server: Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 510624862, index ID 2, page (1:11889147). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 3. Keys out of order on page (1:11931268), slots 206 and 207.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 3. Keys out of order on page (1:11931268), slots 209 and 210.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 3. Keys out of order on page (1:11931268), slots 210 and 211.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 3. Keys out of order on page (1:11931268), slots 230 and 231.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 510624862, index ID 11: Page (1:11988308) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 510624862, index ID 11, page (1:11988308), row 194. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 7251 and 18.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 510624862, index ID 11: Page (1:12003992) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 510624862, index ID 11, page (1:12003992). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
There are 15576829 rows in 65455 pages for object 'map'.
CHECKDB found 0 allocation errors and 23 consistency errors in table 'map' (object ID 510624862).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1790629422, index ID 16: Page (1:11471717) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1790629422, index ID 16, page (1:11471717), row 88. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 3500 and 28.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1790629422, index ID 16: Page (1:11474112) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1790629422, index ID 16, page (1:11474112), row 108. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 745 and 20.
DBCC results for 'links'.
There are 9919777 rows in 124284 pages for object 'links'.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'links' (object ID 1790629422).
CHECKDB found 0 allocation errors and 36 consistency errors in database 'dbloc'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (dbloc ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

sureshot
Yak Posting Veteran

72 Posts

Posted - 2005-12-02 : 14:42:10

Forgot to mention that I'm running SQL 2000 SP4 on Windows Server 2003 Enterprise Edition. This DB has been running since SQL 2000 no SP and I can't say when the last CHECKDB was run so the error may have been there for a while before SP4.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-12-02 : 14:42:22

Bunch of things to say here, in no particular order.

There's no way to tell if the prod db is corrupt or not without running CHECKDB on it. You must do that ASAP to avoid losing any more data. It could be that the backup got corrupted or that the report db is on dodgy hardware.

The errors suggest you've got bad hardware. You need to do the following (apart from coping with the errors themselves) to figure out what caused the corruption so that you can prevent it re-occuring:
1) look in the SQL errorlog and Windows event logs for evidence of h/w problems
2) ensure all firmware and drivers are up-to-date
3) run diagnostics on your IO sub-system

The best way to get around this is to restore a pristine copy of the database from your backups. If you don't have the right set of backups that allows you to keep data loss within your business requirements then:
1) you're going to have to use repair, which will cause data loss in multiple tables given the errors reported. If you have any inherent business logic (or explicit constraints defined on the data) then these will most likely be broken.
2) you're going to have to come up with a high-availability/disaster recovery strategy that allows you to meet your business requirements (and the SLAs that prevent you running CHECKDBs

Let me know how it goes and if you have any more info.

Good luck.

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources