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.
| Author |
Topic |
|
wmac
Starting Member
2 Posts |
 Posted - 2005-08-01 : 18:52:09
|
| Paul,I recently 'inherited' the EIS database. It looks like the index on table 'PREMIUM_FACT_TABLE' is corrupted. I cannot drop the index. Here is the result from DBCC CHECKDATABASE:Server: Msg 8909, Level 16, State 1, Line 1Table error: Object ID -674037686, index ID 256, page ID (1:201566). The PageId in the page header = (3:324935494).Server: Msg 8909, Level 16, State 1, Line 1Table error: Object ID -674037686, index ID 256, page ID (1:201567). The PageId in the page header = (3:325001030).Server: Msg 8909, Level 16, State 1, Line 1Table error: Object ID -674037686, index ID 256, page ID (1:203870). The PageId in the page header = (3:475930446).Server: Msg 8909, Level 16, State 1, Line 1Table error: Object ID -674037686, index ID 256, page ID (1:203871). The PageId in the page header = (3:475995974).Server: Msg 8928, Level 16, State 1, Line 1Object ID 775673811, index ID 0: Page (1:201565) could not be processed. See other errors for details.Server: Msg 8939, Level 16, State 1, Line 1Table error: Object ID 775673811, index ID 0, page (1:201565). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.Server: Msg 8928, Level 16, State 1, Line 1Object ID 775673811, index ID 0: Page (1:201566) could not be processed. See other errors for details.Server: Msg 8928, Level 16, State 1, Line 1Object ID 775673811, index ID 0: Page (1:201567) could not be processed. See other errors for details.Server: Msg 8928, Level 16, State 1, Line 1Object ID 775673811, index ID 0: Page (1:203869) could not be processed. See other errors for details.Server: Msg 8939, Level 16, State 1, Line 1Table error: Object ID 775673811, index ID 0, page (1:203869). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.CHECKDB found 0 allocation errors and 4 consistency errors in table '(Object ID -674037686)' (object ID -674037686).DBCC results for 'EIS'.DBCC results for 'sysobjects'.There are 67 rows in 2 pages for object 'sysobjects'.DBCC results for 'sysindexes'.There are 73 rows in 7 pages for object 'sysindexes'.DBCC results for 'syscolumns'.There are 691 rows in 52 pages for object 'syscolumns'.DBCC results for 'systypes'.There are 26 rows in 1 pages for object 'systypes'.DBCC results for 'syscomments'.There are 127 rows in 16 pages for object 'syscomments'.DBCC results for 'sysfiles1'.There are 2 rows in 1 pages for object 'sysfiles1'.DBCC results for 'syspermissions'.There are 20 rows in 1 pages for object 'syspermissions'.DBCC results for 'sysusers'.There are 15 rows in 1 pages for object 'sysusers'.DBCC results for 'sysproperties'.There are 0 rows in 0 pages for object 'sysproperties'.DBCC results for 'sysdepends'.There are 309 rows in 2 pages for object 'sysdepends'.DBCC results for 'sysreferences'.There are 0 rows in 0 pages for object 'sysreferences'.DBCC results for 'sysfulltextcatalogs'.There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.DBCC results for 'sysfulltextnotify'.There are 0 rows in 0 pages for object 'sysfulltextnotify'.DBCC results for 'sysfilegroups'.There are 1 rows in 1 pages for object 'sysfilegroups'.DBCC results for 'AGTMASTR'.There are 4372 rows in 398 pages for object 'AGTMASTR'.DBCC results for 'STATECD'.There are 67 rows in 1 pages for object 'STATECD'.DBCC results for 'MKTGREP'.There are 14 rows in 1 pages for object 'MKTGREP'.DBCC results for 'STACCPRD'.There are 168 rows in 1 pages for object 'STACCPRD'.DBCC results for 'EISTABLE_VK'.There are 9 rows in 2 pages for object 'EISTABLE_VK'.DBCC results for 'COVERAGE'.There are 586 rows in 9 pages for object 'COVERAGE'.DBCC results for 'YEARMNTH'.There are 48 rows in 1 pages for object 'YEARMNTH'.DBCC results for 'PremTest1'.There are 0 rows in 0 pages for object 'PremTest1'.DBCC results for 'PREMIUM_FACT_TABLE'.Server: Msg 8928, Level 16, State 1, Line 1Object ID 775673811, index ID 0: Page (1:203870) could not be processed. See other errors for details.Server: Msg 8928, Level 16, State 1, Line 1Object ID 775673811, index ID 0: Page (1:203871) could not be processed. See other errors for details.There are 9853114 rows in 131244 pages for object 'PREMIUM_FACT_TABLE'.CHECKDB found 0 allocation errors and 8 consistency errors in table 'PREMIUM_FACT_TABLE' (object ID 775673811).DBCC results for 'CLAIMS_FACT_TABLE'.There are 1308975 rows in 26180 pages for object 'CLAIMS_FACT_TABLE'.DBCC results for 'RATESET'.There are 142 rows in 1 pages for object 'RATESET'.DBCC results for 'EIS_SQL_STATEMENTS'.There are 1 rows in 1 pages for object 'EIS_SQL_STATEMENTS'.DBCC results for 'dtproperties'.There are 0 rows in 0 pages for object 'dtproperties'.DBCC results for 'JOBSTATUS'.There are 2 rows in 1 pages for object 'JOBSTATUS'.CHECKDB found 0 allocation errors and 12 consistency errors in database 'EIS'.repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (EIS ).DBCC execution completed. If DBCC printed error messages, contact your system administrator.The command DBCC DBREINDEX (Premium_fact_table); returns:Server: Msg 823, Level 24, State 2, Line 1I/O error (torn page) detected during read at offset 0x000000626ba000 in file 'E:\MSSQL\DATA\EIS.mdf'.Please help if you can. Thanks! |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-01 : 19:27:23
|
| One of your disks (hosting E:) has gone bad I'm afraid. You've got at least one torn page and what looks like a bunch of pages with garbage sectors overwriting the page headers. Some of these pages at least are in tables rather than non-clustered indexes so you're looking at data loss unless you have up-to-date backups. Although you could run repair and have it deallocate the pages, you'll still have a bad drive and the errors will occur again and you'll lose more data.Check the Windows event log and SQL errorlogs to find out which drive(s) are bad. You'll need to replace that drive and restore from your backups. If you don't have backups, you'll need to extract out as much data as you can from the corrupt table.ThanksPaul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
 |
|
|
|
|
|
|
|
Consistancy errors again