DB Consist Errors - 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

DB Consist Errors

Author

Topic

JRoesner
Starting Member

11 Posts

Posted - 2005-08-11 : 12:21:32

I have been following alot of posts, and haven't found a solution to my errors.

SQL 2000, SP4 (with AWE hotfix)

Users reported that Excecption Access Violation errors were popping up..

dbcc dbcheck ('dbbp2000',REPAIR_ALLOW_DATA_LOSS ) returns with..

Server: Msg 7995, Level 16, State 3, Line 1
Database 'dbBP2000' consistency errors in sysobjects, sysindexes, syscolumns, or systypes prevent further CHECK processing.
DBCC results for 'dbbp2000'.

DBCC dbcheck Ran, details...

============================================================
Server: Msg 7995, Level 16, State 3, Line 1
Database 'dbBP2000' consistency errors in sysobjects, sysindexes, syscolumns, or systypes prevent further CHECK processing.
DBCC results for 'dbbp2000'.
DBCC results for 'sysobjects'.
There are 1046 rows in 19 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 1371 rows in 85 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 7886 rows in 202 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 27 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 973 rows in 319 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 50 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 17 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 1 rows in 1 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 10401 rows in 68 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 31 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 1 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 'tblB2Line'.
There are 407 rows in 17 pages for object 'tblB2Line'.
DBCC results for 'tblK84'.
There are 301540 rows in 4712 pages for object 'tblK84'.
DBCC results for 'tblB2Header'.
There are 187 rows in 5 pages for object 'tblB2Header'.
DBCC results for 'tblCadexConfig'.
There are 4 rows in 1 pages for object 'tblCadexConfig'.
DBCC results for 'tblB2'.
There are 187 rows in 2 pages for object 'tblB2'.
DBCC results for 'tblFreightQuote'.
There are 0 rows in 0 pages for object 'tblFreightQuote'.
DBCC results for 'tblReportFeeSchedule'.
There are 0 rows in 0 pages for object 'tblReportFeeSchedule'.
DBCC results for 'Codes_Container'.
There are 125 rows in 1 pages for object 'Codes_Container'.
DBCC results for 'Codes_Country'.
There are 302 rows in 3 pages for object 'Codes_Country'.
DBCC results for 'Codes_GST'.
There are 38 rows in 1 pages for object 'Codes_GST'.
DBCC results for 'Codes_SuffWarehouse'.
There are 1615 rows in 36 pages for object 'Codes_SuffWarehouse'.
DBCC results for 'Codes_PortOfExit'.
There are 528 rows in 3 pages for object 'Codes_PortOfExit'.
DBCC results for 'tblFreightQuoteDetails'.
There are 0 rows in 0 pages for object 'tblFreightQuoteDetails'.
DBCC results for 'Codes_ReleasePort'.
There are 297 rows in 3 pages for object 'Codes_ReleasePort'.
DBCC results for 'Codes_TariffTreat'.
There are 13 rows in 1 pages for object 'Codes_TariffTreat'.
DBCC results for 'tblProductOGD'.
There are 7 rows in 1 pages for object 'tblProductOGD'.
DBCC results for 'dtproperties'.
There are 28 rows in 1 pages for object 'dtproperties'.
DBCC results for 'List_D8Memorand'.
There are 128 rows in 1 pages for object 'List_D8Memorand'.
DBCC results for 'tempReportInactive'.
There are 122117 rows in 207 pages for object 'tempReportInactive'.
DBCC results for 'tblB3Header'.
There are 471135 rows in 20223 pages for object 'tblB3Header'.
DBCC results for 'tblNafta'.
There are 24292 rows in 175 pages for object 'tblNafta'.
DBCC results for 'tblBankAccountType'.
There are 3 rows in 1 pages for object 'tblBankAccountType'.
DBCC results for 'tblProductChild'.
There are 130222 rows in 1001 pages for object 'tblProductChild'.
DBCC results for 'tblFreightProduct'.
There are 0 rows in 0 pages for object 'tblFreightProduct'.
DBCC results for 'tblFreightCarrier'.
There are 0 rows in 0 pages for object 'tblFreightCarrier'.
DBCC results for 'Codes_AirsAppC'.
There are 130 rows in 2 pages for object 'Codes_AirsAppC'.
DBCC results for 'tblCADEXInfoMsg'.
There are 567 rows in 4 pages for object 'tblCADEXInfoMsg'.
DBCC results for 'tblProductOGDReg'.
There are 3 rows in 1 pages for object 'tblProductOGDReg'.
DBCC results for 'tblCadexInputLog'.
There are 2211211 rows in 47824 pages for object 'tblCadexInputLog'.
DBCC results for 'tblCFIACodes'.
There are 0 rows in 0 pages for object 'tblCFIACodes'.
DBCC results for 'tblCadexQuery'.
There are 18 rows in 1 pages for object 'tblCadexQuery'.
DBCC results for 'tblChargeUserDefined'.
There are 12 rows in 1 pages for object 'tblChargeUserDefined'.
DBCC results for 'tblCadexQueue'.
There are 192689 rows in 1708 pages for object 'tblCadexQueue'.
DBCC results for 'tblShipmentLeg'.
There are 0 rows in 0 pages for object 'tblShipmentLeg'.
DBCC results for 'tblCCIHeader'.
There are 384143 rows in 13657 pages for object 'tblCCIHeader'.
DBCC results for 'ExcludeFromUnbilledTransactionReport'.
There are 38 rows in 1 pages for object 'ExcludeFromUnbilledTransactionReport'.
DBCC results for 'tblFreightBatch'.
There are 0 rows in 0 pages for object 'tblFreightBatch'.
DBCC results for 'tblChargeType'.
There are 22 rows in 3 pages for object 'tblChargeType'.
DBCC results for 'tblCompany'.
There are 40463 rows in 952 pages for object 'tblCompany'.
DBCC results for 'tblContact'.
There are 9166 rows in 184 pages for object 'tblContact'.
DBCC results for 'Codes_Across_Error'.
There are 0 rows in 0 pages for object 'Codes_Across_Error'.
DBCC results for 'tblHarmonizedTariff'.
There are 75767 rows in 948 pages for object 'tblHarmonizedTariff'.
DBCC results for 'codes_ISO3166'.
There are 240 rows in 2 pages for object 'codes_ISO3166'.
DBCC results for 'tblDepartment'.
There are 11 rows in 1 pages for object 'tblDepartment'.
DBCC results for 'tblFreightBatchStatus'.
There are 0 rows in 0 pages for object 'tblFreightBatchStatus'.
DBCC results for 'tblHarmonizedTariffRate'.
There are 341223 rows in 3075 pages for object 'tblHarmonizedTariffRate'.
DBCC results for 'tblCLVS'.
There are 415363 rows in 6740 pages for object 'tblCLVS'.
DBCC results for 'tblPostQueue'.
There are 0 rows in 0 pages for object 'tblPostQueue'.
DBCC results for 'tblFeeScheduleHeader'.
There are 451 rows in 7 pages for object 'tblFeeScheduleHeader'.
DBCC results for 'tblEDI857Header'.
There are 0 rows in 0 pages for object 'tblEDI857Header'.
DBCC results for 'tblTariffExciseDuty'.
There are 256 rows in 3 pages for object 'tblTariffExciseDuty'.
DBCC results for 'tblImpXCompany'.
There are 4067135 rows in 76836 pages for object 'tblImpXCompany'.
DBCC results for 'tblTariffExciseTax'.
There are 0 rows in 0 pages for object 'tblTariffExciseTax'.
DBCC results for 'tblInvoiceBatch'.
There are 15373 rows in 348 pages for object 'tblInvoiceBatch'.
DBCC results for 'tblTariffExciseTaxDetail'.
There are 0 rows in 0 pages for object 'tblTariffExciseTaxDetail'.
DBCC results for 'tblTariffGST'.
There are 0 rows in 0 pages for object 'tblTariffGST'.
DBCC results for 'tblManifest'.
There are 488428 rows in 16846 pages for object 'tblManifest'.
DBCC results for 'tblEDI857InvoiceHeader'.
There are 0 rows in 0 pages for object 'tblEDI857InvoiceHeader'.
DBCC results for 'tblPaperClip'.
There are 606705 rows in 6410 pages for object 'tblPaperClip'.
DBCC results for 'tblPostToAR'.
There are 1992 rows in 5 pages for object 'tblPostToAR'.
DBCC results for 'tblReturnCodes'.
There are 0 rows in 1 pages for object 'tblReturnCodes'.
DBCC results for 'tblCCILineOGDReg'.
There are 1 rows in 1 pages for object 'tblCCILineOGDReg'.
DBCC results for 'tblRMD'.
There are 289908 rows in 12536 pages for object 'tblRMD'.
DBCC results for 'tblReports'.
There are 49 rows in 1 pages for object 'tblReports'.
DBCC results for 'tblRMDAuthorization'.
There are 9054 rows in 77 pages for object 'tblRMDAuthorization'.
DBCC results for 'tblRMDLine'.
There are 278079 rows in 1061 pages for object 'tblRMDLine'.
DBCC results for 'tblCLVSDiscount'.
There are 114961 rows in 410 pages for object 'tblCLVSDiscount'.
DBCC results for 'tblSalesTerritory'.
There are 34 rows in 1 pages for object 'tblSalesTerritory'.
DBCC results for 'tblShipmentUOM'.
There are 125 rows in 1 pages for object 'tblShipmentUOM'.
DBCC results for 'tblEDI857CCILine'.
There are 0 rows in 0 pages for object 'tblEDI857CCILine'.
DBCC results for 'tblInvoiceSystemComment'.
There are 0 rows in 0 pages for object 'tblInvoiceSystemComment'.
DBCC results for 'tblCountry'.
There are 301 rows in 3 pages for object 'tblCountry'.
DBCC results for 'tbluser'.
There are 274 rows in 5 pages for object 'tbluser'.
DBCC results for 'tblSpecialAuthority'.
There are 52 rows in 1 pages for object 'tblSpecialAuthority'.
DBCC results for 'tblProduct'.
There are 136002 rows in 5296 pages for object 'tblProduct'.
DBCC results for 'tblB3SubHeader'.
There are 630044 rows in 10945 pages for object 'tblB3SubHeader'.
DBCC results for 'tblBranch'.
There are 6 rows in 1 pages for object 'tblBranch'.
DBCC results for 'List_Carriers'.
There are 3604 rows in 25 pages for object 'List_Carriers'.
DBCC results for 'tblChargeCode'.
There are 91 rows in 2 pages for object 'tblChargeCode'.
DBCC results for 'tblContactMap'.
There are 9740 rows in 117 pages for object 'tblContactMap'.
DBCC results for 'tblExporter'.
There are 840 rows in 12 pages for object 'tblExporter'.
DBCC results for 'tblExportTo'.
There are 1 rows in 1 pages for object 'tblExportTo'.
DBCC results for 'tblFeeScheduleLine'.
There are 2877 rows in 12 pages for object 'tblFeeScheduleLine'.
DBCC results for 'tblCustomerNoCharge'.
There are 17 rows in 1 pages for object 'tblCustomerNoCharge'.
DBCC results for 'tblFreight'.
There are 321 rows in 5 pages for object 'tblFreight'.
DBCC results for 'tblExitPort'.
There are 0 rows in 0 pages for object 'tblExitPort'.
DBCC results for 'tblImporter'.
There are 5042 rows in 94 pages for object 'tblImporter'.
DBCC results for 'Codes_UnitOfMeas'.
There are 63 rows in 1 pages for object 'Codes_UnitOfMeas'.
DBCC results for 'tblVendor'.
There are 29685 rows in 403 pages for object 'tblVendor'.
DBCC results for 'tblWarehouse'.
There are 29 rows in 1 pages for object 'tblWarehouse'.
DBCC results for 'tblCustomerDefaultCharge'.
There are 11935 rows in 104 pages for object 'tblCustomerDefaultCharge'.
DBCC results for 'tblCadexAcrossErrorCodes'.
There are 684 rows in 14 pages for object 'tblCadexAcrossErrorCodes'.
DBCC results for 'tblB3XRef'.
There are 3045959 rows in 13081 pages for object 'tblB3XRef'.
DBCC results for 'tblNaftaLines'.
There are 88 rows in 2 pages for object 'tblNaftaLines'.
DBCC results for 'tblNAICS'.
There are 0 rows in 1 pages for object 'tblNAICS'.
DBCC results for 'tblInvoiceHeader'.
There are 566081 rows in 17216 pages for object 'tblInvoiceHeader'.
DBCC results for 'terr'.
There are 792 rows in 3 pages for object 'terr'.
DBCC results for 'tblCCILineOGD'.
There are 1100522 rows in 7241 pages for object 'tblCCILineOGD'.
DBCC results for 'tblInvoiceStasis'.
There are 22337 rows in 730 pages for object 'tblInvoiceStasis'.
DBCC results for 'tblRMDOGD'.
There are 15548 rows in 64 pages for object 'tblRMDOGD'.
DBCC results for 'tblSplitTransaction'.
There are 0 rows in 0 pages for object 'tblSplitTransaction'.
DBCC results for 'tblCLVSTransaction'.
There are 0 rows in 1 pages for object 'tblCLVSTransaction'.
DBCC results for 'tblCCILine'.
There are 2772771 rows in 65029 pages for object 'tblCCILine'.
DBCC results for 'tblUnit'.
There are 0 rows in 1 pages for object 'tblUnit'.
DBCC results for 'tblMaxBrokerage'.
There are 63 rows in 1 pages for object 'tblMaxBrokerage'.
DBCC results for 'CODES_B2LegislativeAuthority'.
There are 17 rows in 1 pages for object 'CODES_B2LegislativeAuthority'.
DBCC results for 'tblWarehouseCodes'.
There are 0 rows in 1 pages for object 'tblWarehouseCodes'.
DBCC results for 'tblB2HeaderLegislativeAuthority'.
There are 169 rows in 1 pages for object 'tblB2HeaderLegislativeAuthority'.
DBCC results for 'TempIDS'.
There are 0 rows in 1 pages for object 'TempIDS'.
DBCC results for 'tblCustomerAccount'.
There are 3924 rows in 138 pages for object 'tblCustomerAccount'.
DBCC results for 'tblB2HeaderTypeOfRequest'.
There are 160 rows in 1 pages for object 'tblB2HeaderTypeOfRequest'.
DBCC results for 'CODES_B2TypeOfRequest'.
There are 3 rows in 1 pages for object 'CODES_B2TypeOfRequest'.
DBCC results for 'tblExchangeRate'.
There are 297731 rows in 2919 pages for object 'tblExchangeRate'.
DBCC results for 'Codes_AirsEndUse'.
There are 0 rows in 0 pages for object 'Codes_AirsEndUse'.
DBCC results for 'tblB2Change'.
There are 224 rows in 1 pages for object 'tblB2Change'.
DBCC results for 'Codes_AirsMisc'.
There are 0 rows in 1 pages for object 'Codes_AirsMisc'.
DBCC results for 'tblB3Line'.
There are 1247102 rows in 55548 pages for object 'tblB3Line'.
DBCC results for 'tblMotorstate'.
There are 16659 rows in 231 pages for object 'tblMotorstate'.
DBCC results for 'tblSprech'.
There are 1358 rows in 11 pages for object 'tblSprech'.
DBCC results for 'tblCustomsPort'.
There are 0 rows in 0 pages for object 'tblCustomsPort'.
DBCC results for 'tblOGDHSNumber'.
There are 0 rows in 1 pages for object 'tblOGDHSNumber'.
DBCC results for 'tblRMDACI'.
There are 8067 rows in 72 pages for object 'tblRMDACI'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'dbBP2000'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Any help is appreciated.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-11 : 13:13:37

One of your system tables is corrupt - you cannot repair this and so you must restore from a backup. Can you do the following and post the results so we can work out exactly what the corruption is?

USE dbbp2000
go
DBCC CHECKTABLE (sysindexes) WITH NO_INFOMSGS
go
DBCC CHECKTABLE (sysobjects) WITH NO_INFOMSGS
go
DBCC CHECKTABLE (syscolumns) WITH NO_INFOMSGS
go

You should never just run repair without having an idea what its going to do. REPAIR_ALLOW_DATA_LOSS is deliberately named because it will usually have to delete some data (as a side-effect of a repair) to fix the errors. You should also work out why corruption occured before removing all the evidence.

Can you also checkin the Windows event logs and SQL errorlog for any evidence of hardware issues? Do you have all the latest revs of relevant firmware?

Has anyone tried to do any manual updates to any of these tables?

Thanks

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

JRoesner
Starting Member

11 Posts

Posted - 2005-08-11 : 13:24:10

We just updated firmware about the 1st on the servers without incident.

No manual update of tables has occured.

I was able to pull the tapes from our offsite location, and it was determined that losing 12 hours of data was worth it to have the system back online.

Event logs, etc do not show any indication of hardware problems, and I checked with some friends before we ran the repair against it, figuring that if all else, we could go with the restore once the tapes arrived back in the office.

I will be restoring the db onto a test server to get the information so that we can establish what had happened.

Thanks for the insanely quick response.
John

JRoesner
Starting Member

11 Posts

Posted - 2005-08-11 : 15:46:35

USE dbbp2000
go
DBCC CHECKTABLE (sysindexes) WITH NO_INFOMSGS
go
DBCC CHECKTABLE (sysobjects) WITH NO_INFOMSGS
go
DBCC CHECKTABLE (syscolumns) WITH NO_INFOMSGS
go

Results:

The command(s) completed successfully.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-11 : 16:25:50

I forgot to include systypes too, can you run CHECKTABLE on that too please?

Also, can you run a CHECKDB just to make sure the restored database still shows the problem?

Thanks

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

JRoesner
Starting Member

11 Posts

Posted - 2005-08-11 : 16:33:12

USE dbbp2000
go
DBCC CHECKTABLE (systypes) WITH NO_INFOMSGS
go

=====================
The command(s) completed successfully.

DB on test server, still has the same inconsitancies.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-11 : 17:10:53

quote:
Originally posted by JRoesner

USE dbbp2000
go
DBCC CHECKTABLE (systypes) WITH NO_INFOMSGS
go

=====================
The command(s) completed successfully.

DB on test server, still has the same inconsitancies.

Are the CHECKTABLEs results the same on the test server DB too?

From reading the code, it looks like one of the rows in sysindexes has an invalid key count.

Can you do:

select id, indid from sysindexes where keycnt > 33

Thanks

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

JRoesner
Starting Member

11 Posts

Posted - 2005-08-11 : 17:59:39

id indid
----------- ------
1477580302 20
1477580302 22

(2 row(s) affected)

and yes, the check is identical.

Thanks

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-11 : 18:44:02

ok - this is the problem then - I've never seen this corruption before!

We may be able to fix this without any loss of data or work after all, but we still need to work out why it happened.

Can you do:

select id, indid, name, keycnt from sysindexes where keycnt > 33

I have a hunch that they may be stats indexes.

Once I have this info I'll be able to look through our bug databases and see if there's a known issue that could have caused this. I'll also be able to work out if you can fix it.

Thanks

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

JRoesner
Starting Member

11 Posts

Posted - 2005-08-11 : 19:12:04

Another group was doing some memory testing on that server, and rebooted it.

I went back in, and ran the sql statement you gave me,

select id, indid from sysindexes where keycnt > 33

and nothing came back

I ran the original one of

select id, indid from sysindexes where keycnt > 33

and nothing came back either.

This is wierd. The only thing that happend, (according to the other group) was that the sql server min memory was jacked up from 0... 38xx to use minimum 2 gigs of ram.

I reran the dbcc check

DBCC results for 'dbBP2000'.
DBCC results for 'sysobjects'.
There are 1046 rows in 19 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 1371 rows in 85 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 7886 rows in 202 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 27 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 973 rows in 319 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 50 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 17 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 1 rows in 1 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 10401 rows in 68 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 31 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 1 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 'tblB2Line'.
There are 407 rows in 17 pages for object 'tblB2Line'.
DBCC results for 'tblK84'.
There are 301540 rows in 4712 pages for object 'tblK84'.
DBCC results for 'tblB2Header'.
There are 187 rows in 5 pages for object 'tblB2Header'.
DBCC results for 'tblCadexConfig'.
There are 4 rows in 1 pages for object 'tblCadexConfig'.
DBCC results for 'tblB2'.
There are 187 rows in 2 pages for object 'tblB2'.
DBCC results for 'tblFreightQuote'.
There are 0 rows in 0 pages for object 'tblFreightQuote'.
DBCC results for 'tblReportFeeSchedule'.
There are 0 rows in 0 pages for object 'tblReportFeeSchedule'.
DBCC results for 'Codes_Container'.
There are 125 rows in 1 pages for object 'Codes_Container'.
DBCC results for 'Codes_Country'.
There are 302 rows in 3 pages for object 'Codes_Country'.
DBCC results for 'Codes_GST'.
There are 38 rows in 1 pages for object 'Codes_GST'.
DBCC results for 'Codes_SuffWarehouse'.
There are 1615 rows in 36 pages for object 'Codes_SuffWarehouse'.
DBCC results for 'Codes_PortOfExit'.
There are 528 rows in 3 pages for object 'Codes_PortOfExit'.
DBCC results for 'tblFreightQuoteDetails'.
There are 0 rows in 0 pages for object 'tblFreightQuoteDetails'.
DBCC results for 'Codes_ReleasePort'.
There are 297 rows in 3 pages for object 'Codes_ReleasePort'.
DBCC results for 'Codes_TariffTreat'.
There are 13 rows in 1 pages for object 'Codes_TariffTreat'.
DBCC results for 'tblProductOGD'.
There are 7 rows in 1 pages for object 'tblProductOGD'.
DBCC results for 'dtproperties'.
There are 28 rows in 1 pages for object 'dtproperties'.
DBCC results for 'List_D8Memorand'.
There are 128 rows in 1 pages for object 'List_D8Memorand'.
DBCC results for 'tempReportInactive'.
There are 122117 rows in 207 pages for object 'tempReportInactive'.
DBCC results for 'tblB3Header'.
There are 471182 rows in 20294 pages for object 'tblB3Header'.
DBCC results for 'tblNafta'.
There are 24288 rows in 176 pages for object 'tblNafta'.
DBCC results for 'tblBankAccountType'.
There are 3 rows in 1 pages for object 'tblBankAccountType'.
DBCC results for 'tblProductChild'.
There are 130232 rows in 1001 pages for object 'tblProductChild'.
DBCC results for 'tblFreightProduct'.
There are 0 rows in 0 pages for object 'tblFreightProduct'.
DBCC results for 'tblFreightCarrier'.
There are 0 rows in 0 pages for object 'tblFreightCarrier'.
DBCC results for 'Codes_AirsAppC'.
There are 130 rows in 2 pages for object 'Codes_AirsAppC'.
DBCC results for 'tblCADEXInfoMsg'.
There are 567 rows in 4 pages for object 'tblCADEXInfoMsg'.
DBCC results for 'tblProductOGDReg'.
There are 3 rows in 1 pages for object 'tblProductOGDReg'.
DBCC results for 'tblCadexInputLog'.
There are 2211211 rows in 47824 pages for object 'tblCadexInputLog'.
DBCC results for 'tblCFIACodes'.
There are 0 rows in 0 pages for object 'tblCFIACodes'.
DBCC results for 'tblCadexQuery'.
There are 18 rows in 1 pages for object 'tblCadexQuery'.
DBCC results for 'tblChargeUserDefined'.
There are 12 rows in 1 pages for object 'tblChargeUserDefined'.
DBCC results for 'tblCadexQueue'.
There are 192689 rows in 1708 pages for object 'tblCadexQueue'.
DBCC results for 'tblShipmentLeg'.
There are 0 rows in 0 pages for object 'tblShipmentLeg'.
DBCC results for 'tblCCIHeader'.
There are 384479 rows in 13665 pages for object 'tblCCIHeader'.
DBCC results for 'ExcludeFromUnbilledTransactionReport'.
There are 38 rows in 1 pages for object 'ExcludeFromUnbilledTransactionReport'.
DBCC results for 'tblFreightBatch'.
There are 0 rows in 0 pages for object 'tblFreightBatch'.
DBCC results for 'tblChargeType'.
There are 22 rows in 3 pages for object 'tblChargeType'.
DBCC results for 'tblCompany'.
There are 40462 rows in 952 pages for object 'tblCompany'.
DBCC results for 'tblContact'.
There are 9166 rows in 184 pages for object 'tblContact'.
DBCC results for 'Codes_Across_Error'.
There are 0 rows in 0 pages for object 'Codes_Across_Error'.
DBCC results for 'tblHarmonizedTariff'.
There are 75767 rows in 948 pages for object 'tblHarmonizedTariff'.
DBCC results for 'codes_ISO3166'.
There are 240 rows in 2 pages for object 'codes_ISO3166'.
DBCC results for 'tblDepartment'.
There are 11 rows in 1 pages for object 'tblDepartment'.
DBCC results for 'tblFreightBatchStatus'.
There are 0 rows in 0 pages for object 'tblFreightBatchStatus'.
DBCC results for 'tblHarmonizedTariffRate'.
There are 341223 rows in 3075 pages for object 'tblHarmonizedTariffRate'.
DBCC results for 'tblCLVS'.
There are 415363 rows in 6740 pages for object 'tblCLVS'.
DBCC results for 'tblPostQueue'.
There are 0 rows in 0 pages for object 'tblPostQueue'.
DBCC results for 'tblFeeScheduleHeader'.
There are 451 rows in 7 pages for object 'tblFeeScheduleHeader'.
DBCC results for 'tblEDI857Header'.
There are 0 rows in 0 pages for object 'tblEDI857Header'.
DBCC results for 'tblTariffExciseDuty'.
There are 256 rows in 3 pages for object 'tblTariffExciseDuty'.
DBCC results for 'tblImpXCompany'.
There are 4068675 rows in 76867 pages for object 'tblImpXCompany'.
DBCC results for 'tblTariffExciseTax'.
There are 0 rows in 0 pages for object 'tblTariffExciseTax'.
DBCC results for 'tblInvoiceBatch'.
There are 15382 rows in 348 pages for object 'tblInvoiceBatch'.
DBCC results for 'tblTariffExciseTaxDetail'.
There are 0 rows in 0 pages for object 'tblTariffExciseTaxDetail'.
DBCC results for 'tblTariffGST'.
There are 0 rows in 0 pages for object 'tblTariffGST'.
DBCC results for 'tblManifest'.
There are 488532 rows in 16855 pages for object 'tblManifest'.
DBCC results for 'tblEDI857InvoiceHeader'.
There are 0 rows in 0 pages for object 'tblEDI857InvoiceHeader'.
DBCC results for 'tblPaperClip'.
There are 606794 rows in 6415 pages for object 'tblPaperClip'.
DBCC results for 'tblPostToAR'.
There are 2000 rows in 5 pages for object 'tblPostToAR'.
DBCC results for 'tblReturnCodes'.
There are 0 rows in 1 pages for object 'tblReturnCodes'.
DBCC results for 'tblCCILineOGDReg'.
There are 1 rows in 1 pages for object 'tblCCILineOGDReg'.
DBCC results for 'tblRMD'.
There are 289996 rows in 12542 pages for object 'tblRMD'.
DBCC results for 'tblReports'.
There are 49 rows in 1 pages for object 'tblReports'.
DBCC results for 'tblRMDAuthorization'.
There are 9065 rows in 77 pages for object 'tblRMDAuthorization'.
DBCC results for 'tblRMDLine'.
There are 278142 rows in 1061 pages for object 'tblRMDLine'.
DBCC results for 'tblCLVSDiscount'.
There are 114937 rows in 409 pages for object 'tblCLVSDiscount'.
DBCC results for 'tblSalesTerritory'.
There are 34 rows in 1 pages for object 'tblSalesTerritory'.
DBCC results for 'tblShipmentUOM'.
There are 125 rows in 1 pages for object 'tblShipmentUOM'.
DBCC results for 'tblEDI857CCILine'.
There are 0 rows in 0 pages for object 'tblEDI857CCILine'.
DBCC results for 'tblInvoiceSystemComment'.
There are 0 rows in 0 pages for object 'tblInvoiceSystemComment'.
DBCC results for 'tblCountry'.
There are 301 rows in 3 pages for object 'tblCountry'.
DBCC results for 'tbluser'.
There are 274 rows in 5 pages for object 'tbluser'.
DBCC results for 'tblSpecialAuthority'.
There are 52 rows in 1 pages for object 'tblSpecialAuthority'.
DBCC results for 'tblProduct'.
There are 136053 rows in 5297 pages for object 'tblProduct'.
DBCC results for 'tblB3SubHeader'.
There are 630143 rows in 10958 pages for object 'tblB3SubHeader'.
DBCC results for 'tblBranch'.
There are 6 rows in 1 pages for object 'tblBranch'.
DBCC results for 'List_Carriers'.
There are 3604 rows in 25 pages for object 'List_Carriers'.
DBCC results for 'tblChargeCode'.
There are 91 rows in 2 pages for object 'tblChargeCode'.
DBCC results for 'tblContactMap'.
There are 9741 rows in 117 pages for object 'tblContactMap'.
DBCC results for 'tblExporter'.
There are 840 rows in 12 pages for object 'tblExporter'.
DBCC results for 'tblExportTo'.
There are 1 rows in 1 pages for object 'tblExportTo'.
DBCC results for 'tblFeeScheduleLine'.
There are 2877 rows in 12 pages for object 'tblFeeScheduleLine'.
DBCC results for 'tblCustomerNoCharge'.
There are 17 rows in 1 pages for object 'tblCustomerNoCharge'.
DBCC results for 'tblFreight'.
There are 321 rows in 5 pages for object 'tblFreight'.
DBCC results for 'tblExitPort'.
There are 0 rows in 0 pages for object 'tblExitPort'.
DBCC results for 'tblImporter'.
There are 5043 rows in 94 pages for object 'tblImporter'.
DBCC results for 'Codes_UnitOfMeas'.
There are 63 rows in 1 pages for object 'Codes_UnitOfMeas'.
DBCC results for 'tblInvoiceLine'.
There are 1768185 rows in 29906 pages for object 'tblInvoiceLine'.
DBCC results for 'tblVendor'.
There are 29683 rows in 403 pages for object 'tblVendor'.
DBCC results for 'tblWarehouse'.
There are 29 rows in 1 pages for object 'tblWarehouse'.
DBCC results for 'tblCustomerDefaultCharge'.
There are 11941 rows in 104 pages for object 'tblCustomerDefaultCharge'.
DBCC results for 'tblCadexAcrossErrorCodes'.
There are 684 rows in 14 pages for object 'tblCadexAcrossErrorCodes'.
DBCC results for 'tblB3XRef'.
There are 3048547 rows in 13089 pages for object 'tblB3XRef'.
DBCC results for 'tblNaftaLines'.
There are 88 rows in 2 pages for object 'tblNaftaLines'.
DBCC results for 'tblNAICS'.
There are 0 rows in 1 pages for object 'tblNAICS'.
DBCC results for 'tblInvoiceHeader'.
There are 566436 rows in 17248 pages for object 'tblInvoiceHeader'.
DBCC results for 'terr'.
There are 792 rows in 3 pages for object 'terr'.
DBCC results for 'tblCCILineOGD'.
There are 1100522 rows in 7241 pages for object 'tblCCILineOGD'.
DBCC results for 'tblInvoiceStasis'.
There are 22230 rows in 730 pages for object 'tblInvoiceStasis'.
DBCC results for 'tblRMDOGD'.
There are 15548 rows in 64 pages for object 'tblRMDOGD'.
DBCC results for 'tblSplitTransaction'.
There are 0 rows in 0 pages for object 'tblSplitTransaction'.
DBCC results for 'tblCLVSTransaction'.
There are 0 rows in 1 pages for object 'tblCLVSTransaction'.
DBCC results for 'tblCCILine'.
There are 2774700 rows in 65075 pages for object 'tblCCILine'.
DBCC results for 'tblUnit'.
There are 0 rows in 1 pages for object 'tblUnit'.
DBCC results for 'tblMaxBrokerage'.
There are 63 rows in 1 pages for object 'tblMaxBrokerage'.
DBCC results for 'CODES_B2LegislativeAuthority'.
There are 17 rows in 1 pages for object 'CODES_B2LegislativeAuthority'.
DBCC results for 'tblWarehouseCodes'.
There are 0 rows in 1 pages for object 'tblWarehouseCodes'.
DBCC results for 'tblB2HeaderLegislativeAuthority'.
There are 169 rows in 1 pages for object 'tblB2HeaderLegislativeAuthority'.
DBCC results for 'TempIDS'.
There are 0 rows in 1 pages for object 'TempIDS'.
DBCC results for 'tblCustomerAccount'.
There are 3925 rows in 138 pages for object 'tblCustomerAccount'.
DBCC results for 'tblB2HeaderTypeOfRequest'.
There are 160 rows in 1 pages for object 'tblB2HeaderTypeOfRequest'.
DBCC results for 'CODES_B2TypeOfRequest'.
There are 3 rows in 1 pages for object 'CODES_B2TypeOfRequest'.
DBCC results for 'tblExchangeRate'.
There are 297800 rows in 2920 pages for object 'tblExchangeRate'.
DBCC results for 'Codes_AirsEndUse'.
There are 0 rows in 0 pages for object 'Codes_AirsEndUse'.
DBCC results for 'tblB2Change'.
There are 224 rows in 1 pages for object 'tblB2Change'.
DBCC results for 'Codes_AirsMisc'.
There are 0 rows in 1 pages for object 'Codes_AirsMisc'.
DBCC results for 'tblB3Line'.
There are 1247514 rows in 55567 pages for object 'tblB3Line'.
DBCC results for 'tblMotorstate'.
There are 16659 rows in 231 pages for object 'tblMotorstate'.
DBCC results for 'tblSprech'.
There are 1358 rows in 11 pages for object 'tblSprech'.
DBCC results for 'tblCustomsPort'.
There are 0 rows in 0 pages for object 'tblCustomsPort'.
DBCC results for 'tblOGDHSNumber'.
There are 0 rows in 1 pages for object 'tblOGDHSNumber'.
DBCC results for 'tblRMDACI'.
There are 8072 rows in 72 pages for object 'tblRMDACI'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'dbBP2000'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

WTF? I rechecked the DB, it hasnt changed, no maintenance has been done. There are no jobs on this server. While it is *identical* to the other server the DB was sitting on, the only difference was the min memory requirement of 2 gigs for sql (which it still is using only like 50 megs according to task mon). SQL agent isnt running on this machine either.

I rebooted, reset it back to the 0 min memory, and rerun the sql commands - and they are the same. it is as if the problem has disappeared.

Which of course, means I am very worried.. and stumped.. but more worried.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-11 : 19:21:56

That's very weird.

Two things spring to mind:
1) these two indexes were stats indexes that got dropped/recreated somehow when the server was restarted.
2) if you're talking about the prod server, it could be an in-memory corruption

When you say the server was rebooted, which one? Prod or test?

On whichever one was rebooted, can you do:

select * from sysindexes
where id = 1477580302
and indid > 19
and indid < 23

to see if the two indexes still exist?

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

JRoesner
Starting Member

11 Posts

Posted - 2005-08-11 : 19:25:50

I am in the process, of moving over a "clean" copy of the corrupted DB (that is corrupt) to that server, to see if it is still thinking its corrupt. It will take some time to move over and restore, but I am going to retrace all of the steps posted, to see if it can be duplicated.

JRoesner
Starting Member

11 Posts

Posted - 2005-08-11 : 20:31:13

After a restore, I am able to run the last sql command.

id indid name keycnt
1477580302 20 hind_c_1477580302_7A 34
1477580302 22 hind_c_1477580302_2A 34

(2 row(s) affected)

This database is (so far) remaining corrupt.

JRoesner
Starting Member

11 Posts

Posted - 2005-08-11 : 20:32:47

id status first indid root minlen keycnt groupid dpages reserved used rowcnt rowmodctr reserved3 reserved4 xmaxlen maxirow OrigFillFactor StatVersion reserved2 FirstIAM impid lockflags pgmodctr keys name statblob maxlen rows
----------- ----------- -------------- ------ -------------- ------ ------ ------- ----------- ----------- ----------- -------------------- ----------- --------- --------- ------- ------- -------------- ----------- ----------- -------------- ------ --------- ----------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
1477580302 16432 0x000000000000 20 0x000000000000 193 34 1 0 0 0 1766996 0 0 0 224 230 90 0 0 0x000000000000 0 0 0 0xA703A7000200000008D00034000000000700FFFF00000000FCFFFFFF000000003C013C0008001304000000000000000009000100000000001C000100000001003801380004000A000000000000000000020009000000000008000900000001003C013C0008001304000000000000000009000D00000000001C000D0000000100 hind_c_1477580302_7A 0x040000001E6E1C00AD96000054F61A00000000000000000000000000000000000000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F000080 8000 1766996
1477580302 32 0x000000000000 21 0x000000000000 13 5 1 0 0 0 0 0 0 0 36 42 90 0 0 0x000000000000 0 0 0 0x3801380004000A000000000000000000020001000000000008000100000000003C013C0008001304000000000000000009000500000000001C000500000000003C013C0008001304000000000000000009000500000000001C000100000003003801380004000A00000000000000000002000100000000000800090000000300 hind_1477580302_2A_9A NULL 8000 0
1477580302 16432 0x000000000000 22 0x000000000000 193 34 1 0 0 0 1766996 0 0 0 220 226 90 0 0 0x000000000000 0 0 0 0x3801380004000A000000000000000000020001000000000008000100000000003C013C0008001304000000000000000009000500000000001C000500000001003801380004000A00000000000000000002000D000000000008000D00000003003C013C0008001304000000000000000009001100000000001C00110000000100 hind_c_1477580302_2A 0x040000000E821C00AD96000054F61A00000000000000000000000000000000000000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F0000803F000000000000000000000000000000000000000000000000000000 8000 1766996

(3 row(s) affected)

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-11 : 21:24:40

ok - the two corrupt indexes are hypothetical clustered indexes created when someone ran Index Tuning Wizard (I can tell this from the bits set in the 'status' column).

You've hit a bug (fixed in SP4) that causes index/stats rebuild to treat these incorrectly, and hence the number of columns in them keeps growing. The fix is to get rid of the indexes - I'm checking with the Optimizer team on the safest way for you to do this.

Thankfully, this corruption is benign and none of your data is at risk. I'll let you know what to do as soon as I hear back from them (Friday morning at the latest).

Apologies that you hit this issue and its caused you wasted time.

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

JRoesner
Starting Member

11 Posts

Posted - 2005-08-11 : 21:29:28

The server started the errors when it was at SP3. It only went to SP4, as one of the KB articles stated that a fix for the error was to install SP4.

The test server is still on SP3, and still shows the corrupt state.

I can try and reboot that server, to see if the problem goes away again.

As for the data, the company took the stance to roll back and lose 12 hours of work gauranteed to be back up and running, so for now this is an educational project.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-11 : 21:36:35

Can you point me at the KB article? I don't think just installing SP4 will fix the problem (SP4 contains the fix to stop it happening, but you need to get rid of the corrupt indexes somehow too).

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

JRoesner
Starting Member

11 Posts

Posted - 2005-08-11 : 21:46:09

892451

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-12 : 00:39:31

The bug described in that article isn't the one you've run into with this corruption. I don't believe there's a KB article for it - I'll write one sometime soon to make sure there is.

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

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-12 : 00:54:49

ok - the word from the Optimizer team expert on statistics is that he confirms my diagnosis and you can simply drop these two indexes as they're of no use at all. You should see no ill effects from their presence on SP4 apart from the CHECKDB warning. On SP3 they can cause DBREINDEX, SHOW_STATISTICS and stats rebuilds to AV and/or assert.

A happy ending for once

Thanks for bearing with me while I worked it out.

Paul Randal
Dev Lead, 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