I'm facing problem when delete a record in the ms sql 2005 .
The error message i got is stated below:
No rows were deleted
A problem occured attempting to delete row 1304.
Error: .Net Sql Clint Data Provider
Error Message: Possible index corruption detected. Run DBCC CHECKDB.
Correct the errors and atempt to delete the row again or press ESC to cancel the changes.
just certain record i can not delete and face this problem.
i detach the database and attached in the ms sql 2000, and select the same record to delete. there is no problem at all.
i wander is it ms sql 2005 bugs? can anyone tell me how to fix it .. how to to use the DBCC CHECKDB ?
thanks alot....
P/s : if i posted at the wrong section just let me know, I'm beginner in this forum here ...
thanks...
Just like the error message tells you:
Use a SqlCommand to execute "DBCC CHECKDB" or use SQL Management Studio, create a new empty query and execute DBCC CHECKDB
--
SvenC
so i just type DBCC CHECKDB in SQL Management Studio..... amd i got this result
what it means ? i still can not delete the record in the table. can some one tell me how to solve it ? index corruption error....
DBCC results for 'CPMLife'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
DBCC results for 'sys.sysrowsetcolumns'.
There are 2294 rows in 15 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 199 rows in 2 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 217 rows in 3 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 2294 rows in 17 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 199 rows in 2 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 217 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 156 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 436 rows in 6 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 3114 rows in 33 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 39 rows in 20 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 27 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 1143 rows in 14 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 2391 rows in 10 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysobjvalues'.
There are 1288 rows in 280 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 14 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 133 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 1331 rows in 9 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysqnames'.
There are 92 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 93 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 97 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 17 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 0 rows in 0 pages for object "sys.sysbinsubobjs".
DBCC results for 'IvItemStatus'.
There are 7 rows in 1 pages for object "IvItemStatus".
DBCC results for 'PrShfPlan'.
There are 127 rows in 1 pages for object "PrShfPlan".
DBCC results for 'IvLabor'.
There are 1 rows in 1 pages for object "IvLabor".
DBCC results for 'PrShfPlanDtl'.
There are 181 rows in 1 pages for object "PrShfPlanDtl".
DBCC results for 'PlMRNDtl'.
There are 306 rows in 3 pages for object "PlMRNDtl".
DBCC results for 'PlMRN'.
There are 60 rows in 5 pages for object "PlMRN".
DBCC results for 'sys.queue_messages_121767491'.
There are 0 rows in 0 pages for object "sys.queue_messages_121767491".
DBCC results for 'IvClass'.
There are 18 rows in 1 pages for object "IvClass".
DBCC results for 'IvMthEnd'.
There are 46731 rows in 1252 pages for object "IvMthEnd".
DBCC results for 'sys.queue_messages_153767605'.
There are 0 rows in 0 pages for object "sys.queue_messages_153767605".
DBCC results for 'IvTrxType'.
There are 0 rows in 0 pages for object "IvTrxType".
DBCC results for 'sys.queue_messages_185767719'.
There are 0 rows in 0 pages for object "sys.queue_messages_185767719".
DBCC results for 'IvMachine'.
There are 10 rows in 1 pages for object "IvMachine".
DBCC results for 'PlSchProcessHis'.
There are 0 rows in 0 pages for object "PlSchProcessHis".
DBCC results for 'PlMRP'.
There are 5890 rows in 353 pages for object "PlMRP".
DBCC results for 'PrShift'.
There are 9 rows in 1 pages for object "PrShift".
DBCC results for 'PlMRPMsg'.
There are 58 rows in 8 pages for object "PlMRPMsg".
DBCC results for 'IvICust'.
There are 763 rows in 20 pages for object "IvICust".
DBCC results for 'ExportLog'.
There are 21010 rows in 577 pages for object "ExportLog".
DBCC results for 'PlSchProcess'.
There are 3041 rows in 73 pages for object "PlSchProcess".
DBCC results for 'PlSchDetailHis'.
There are 0 rows in 0 pages for object "PlSchDetailHis".
DBCC results for 'PlSchHis'.
There are 0 rows in 0 pages for object "PlSchHis".
DBCC results for 'PlCRP'.
There are 4877 rows in 132 pages for object "PlCRP".
DBCC results for 'IvTrxBatchPSize'.
There are 1300 rows in 117 pages for object "IvTrxBatchPSize".
DBCC results for 'PlSchDetail'.
There are 9644 rows in 233 pages for object "PlSchDetail".
DBCC results for 'IvIBalPSize'.
There are 2021 rows in 68 pages for object "IvIBalPSize".
DBCC results for 'PlMRPDtl'.
There are 12848 rows in 374 pages for object "PlMRPDtl".
DBCC results for 'SaInvoiceDtl'.
There are 1482 rows in 45 pages for object "SaInvoiceDtl".
DBCC results for 'PrDate'.
There are 0 rows in 0 pages for object "PrDate".
DBCC results for 'IvTrxHisPSize'.
There are 143966 rows in 3351 pages for object "IvTrxHisPSize".
DBCC results for 'PrDefAttachment'.
There are 0 rows in 0 pages for object "PrDefAttachment".
DBCC results for 'PrDefDtl'.
There are 7986 rows in 297 pages for object "PrDefDtl".
DBCC results for 'PlFloor'.
There are 1201 rows in 32 pages for object "PlFloor".
DBCC results for 'IvSubClass'.
There are 81 rows in 3 pages for object "IvSubClass".
DBCC results for 'PrDailyPrdDefect'.
There are 4 rows in 1 pages for object "PrDailyPrdDefect".
DBCC results for 'PODetail'.
There are 8261 rows in 237 pages for object "PODetail".
DBCC results for 'PrDefType'.
There are 1 rows in 1 pages for object "PrDefType".
DBCC results for 'IvMachineDtl'.
There are 10 rows in 1 pages for object "IvMachineDtl".
DBCC results for 'PrPreventive'.
There are 0 rows in 1 pages for object "PrPreventive".
DBCC results for 'PrSemiFGRecover'.
There are 0 rows in 0 pages for object "PrSemiFGRecover".
DBCC results for 'PrShfCal'.
There are 675 rows in 8 pages for object "PrShfCal".
DBCC results for 'ROPathList'.
There are 1 rows in 1 pages for object "ROPathList".
DBCC results for 'PlForecast'.
There are 282 rows in 17 pages for object "PlForecast".
DBCC results for 'POOrder'.
There are 2789 rows in 112 pages for object "POOrder".
DBCC results for 'PrShiftGrp'.
There are 28 rows in 1 pages for object "PrShiftGrp".
DBCC results for 'PrWrkCtr'.
There are 8 rows in 1 pages for object "PrWrkCtr".
DBCC results for 'PrDailyPrd'.
There are 19 rows in 1 pages for object "PrDailyPrd".
DBCC results for 'ROImportLog'.
There are 0 rows in 0 pages for object "ROImportLog".
DBCC results for 'ExportInvToOSK'.
There are 35 rows in 1 pages for object "ExportInvToOSK".
DBCC results for 'ROTemp'.
There are 3067 rows in 104 pages for object "ROTemp".
DBCC results for 'IvCyCnt'.
There are 25083 rows in 880 pages for object "IvCyCnt".
DBCC results for 'CoActFreightOutwards'.
There are 1 rows in 1 pages for object "CoActFreightOutwards".
DBCC results for 'CoIvMthEndWIP'.
There are 1 rows in 1 pages for object "CoIvMthEndWIP".
DBCC results for 'SmBuyer'.
There are 3 rows in 1 pages for object "SmBuyer".
DBCC results for 'CoLaborCostAct'.
There are 2 rows in 1 pages for object "CoLaborCostAct".
DBCC results for 'SmCalendar'.
There are 913 rows in 7 pages for object "SmCalendar".
DBCC results for 'PlSch'.
There are 805 rows in 31 pages for object "PlSch".
DBCC results for 'CYReconIvTrxHis'.
There are 1685 rows in 55 pages for object "CYReconIvTrxHis".
DBCC results for 'CoOverhead'.
There are 7 rows in 1 pages for object "CoOverhead".
DBCC results for 'SmCompany'.
There are 1 rows in 1 pages for object "SmCompany".
DBCC results for 'IMSVariance'.
There are 1388 rows in 19 pages for object "IMSVariance".
DBCC results for 'CoOverheadAct'.
There are 12 rows in 1 pages for object "CoOverheadAct".
DBCC results for 'SmCountry'.
There are 18 rows in 1 pages for object "SmCountry".
DBCC results for 'SmCur'.
There are 8 rows in 1 pages for object "SmCur".
DBCC results for 'IvIVendor'.
There are 613 rows in 21 pages for object "IvIVendor".
DBCC results for 'CoPrDefDtlCSim'.
There are 70 rows in 2 pages for object "CoPrDefDtlCSim".
DBCC results for 'SmDept'.
There are 12 rows in 1 pages for object "SmDept".
DBCC results for 'CPMDelSch'.
There are 154 rows in 8 pages for object "CPMDelSch".
DBCC results for 'SmRegister'.
There are 69521 rows in 1040 pages for object "SmRegister".
DBCC results for 'SaCDNDtl'.
There are 0 rows in 0 pages for object "SaCDNDtl".
DBCC results for 'CoPrDefProcessCSim'.
There are 17 rows in 1 pages for object "CoPrDefProcessCSim".
DBCC results for 'SmGroup'.
There are 6 rows in 1 pages for object "SmGroup".
DBCC results for 'SaCDNHdr'.
There are 0 rows in 0 pages for object "SaCDNHdr".
DBCC results for 'CoStdFreightOutwards'.
There are 3 rows in 1 pages for object "CoStdFreightOutwards".
DBCC results for 'IvMthEndStdCost'.
There are 1455 rows in 10 pages for object "IvMthEndStdCost".
DBCC results for 'SaInvoiceHdr'.
There are 849 rows in 66 pages for object "SaInvoiceHdr".
DBCC results for 'SmNumCuz'.
There are 64 rows in 6 pages for object "SmNumCuz".
DBCC results for 'SaPayTerms'.
There are 0 rows in 0 pages for object "SaPayTerms".
DBCC results for 'SaTraderTax'.
There are 21 rows in 3 pages for object "SaTraderTax".
DBCC results for 'SmPeriod'.
There are 48 rows in 1 pages for object "SmPeriod".
DBCC results for 'SmCurrExRate'.
There are 15 rows in 3 pages for object "SmCurrExRate".
DBCC results for 'PrDefMas'.
There are 561 rows in 19 pages for object "PrDefMas".
DBCC results for 'SmReason'.
There are 55 rows in 1 pages for object "SmReason".
DBCC results for 'SmCurrRate'.
There are 23 rows in 2 pages for object "SmCurrRate".
DBCC results for 'SmSaRep'.
There are 0 rows in 0 pages for object "SmSaRep".
DBCC results for 'SmTraderContact'.
There are 351 rows in 8 pages for object "SmTraderContact".
DBCC results for 'SmTaxCat'.
There are 1 rows in 1 pages for object "SmTaxCat".
DBCC results for 'SmUOM'.
There are 18 rows in 1 pages for object "SmUOM".
DBCC results for 'SmTaxGrp'.
There are 1 rows in 1 pages for object "SmTaxGrp".
DBCC results for 'SmUOMConv'.
There are 1 rows in 1 pages for object "SmUOMConv".
DBCC results for 'SmTraderSub'.
There are 83 rows in 1 pages for object "SmTraderSub".
DBCC results for 'SmUser'.
There are 19 rows in 2 pages for object "SmUser".
DBCC results for 'IvItemTax'.
There are 0 rows in 0 pages for object "IvItemTax".
DBCC results for 'SmWHouse'.
There are 15 rows in 1 pages for object "SmWHouse".
DBCC results for 'POPR'.
There are 0 rows in 0 pages for object "POPR".
DBCC results for 'POPRDtl'.
There are 0 rows in 0 pages for object "POPRDtl".
DBCC results for 'POQuote'.
There are 4 rows in 1 pages for object "POQuote".
DBCC results for 'POQuoteDtl'.
There are 10 rows in 1 pages for object "POQuoteDtl".
DBCC results for 'SmMthBudget'.
There are 9 rows in 2 pages for object "SmMthBudget".
DBCC results for 'SaOrder'.
There are 2484 rows in 85 pages for object "SaOrder".
DBCC results for 'SaOrderDtl'.
There are 5086 rows in 139 pages for object "SaOrderDtl".
DBCC results for 'dtproperties'.
There are 0 rows in 0 pages for object "dtproperties".
DBCC results for 'SmDocNo'.
There are 128 rows in 2 pages for object "SmDocNo".
DBCC results for 'SmTrader'.
There are 309 rows in 30 pages for object "SmTrader".
DBCC results for 'CYReconIMS'.
There are 1084 rows in 28 pages for object "CYReconIMS".
DBCC results for 'SmPara'.
There are 1 rows in 1 pages for object "SmPara".
DBCC results for 'SmMenu'.
There are 231 rows in 3 pages for object "SmMenu".
DBCC results for 'SmGrouprights'.
There are 790 rows in 6 pages for object "SmGrouprights".
DBCC results for 'IvTrxBatchHdr'.
There are 273 rows in 6 pages for object "IvTrxBatchHdr".
DBCC results for 'CoPrDefMasCSim'.
There are 5 rows in 1 pages for object "CoPrDefMasCSim".
DBCC results for 'IvTrxBatch'.
There are 1550 rows in 99 pages for object "IvTrxBatch".
DBCC results for 'IvTrxHis'.
There are 175814 rows in 6613 pages for object "IvTrxHis".
DBCC results for 'CoOverheadCSim'.
There are 35 rows in 1 pages for object "CoOverheadCSim".
DBCC results for 'PrDailyPrdMachine'.
There are 13 rows in 1 pages for object "PrDailyPrdMachine".
DBCC results for 'PrProcess'.
There are 8 rows in 1 pages for object "PrProcess".
DBCC results for 'PrDefProcess'.
There are 2040 rows in 58 pages for object "PrDefProcess".
DBCC results for 'CoSalesInvoice'.
There are 0 rows in 0 pages for object "CoSalesInvoice".
DBCC results for 'SmNum'.
There are 19 rows in 2 pages for object "SmNum".
DBCC results for 'IvIMas'.
There are 1455 rows in 130 pages for object "IvIMas".
DBCC results for 'IvCyCntPSize'.
There are 26708 rows in 640 pages for object "IvCyCntPSize".
DBCC results for 'IvIBal'.
There are 2390 rows in 68 pages for object "IvIBal".
DBCC results for 'IvILoc'.
There are 1647 rows in 85 pages for object "IvILoc".
DBCC results for 'IvILocMas'.
There are 1097 rows in 18 pages for object "IvILocMas".
DBCC results for 'IvISubstitute'.
There are 0 rows in 0 pages for object "IvISubstitute".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'CPMLife'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
|||i found some solution from other thread.
first i set the database in single user mode.
ALTER DATABASE CPMLife SET SINGLE_USER WITH ROLLBACK IMMEDIATE
then i run the code below to fix the database.
DBCC CHECKDB
(
CPMLife ,
REPAIR_ALLOW_DATA_LOSS
)
but i still get the same error message which is not allow me to delete the record in the table.
Try rebuilding the indexes on the table.
DBCC DBReIndex ('TableName')
|||hi,
i have run the code you ask me to try which is
DBCC DBReIndex ('IvMthEnd')
Msg 9100, Level 23, State 2, Line 1
Possible index corruption detected. Run DBCC CHECKDB.
then i go to ru nthe DBCC CHECKDB again. and the result is no error
CHECKDB found 0 allocation errors and 0 consistency errors in database 'CPMLife'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I still can not delete the record and get the same error ... anything else i can do ?
|||You have 2 options:
1. restore from backup.
2. find the offending index(es) and delete/recreate - sp_helpindex should give you a list.|||
i do not have backup version of the database..
so i would like to go to option 2, but how to find the offending index(es) record ? to delete or recreate ?
and guide me more to solve it ?
thanks
|||execute 'sp_helpindex table_name' to get the list of the indexes. Make note of the result before you drop the indexes. To drop the index, execute "drop index".http://msdn2.microsoft.com/en-us/library/ms188771.aspx
http://msdn2.microsoft.com/en-us/library/ms188783.aspx
http://msdn2.microsoft.com/en-us/library/ms176118.aspx
You can post the result here if you need further help.
Actually, before you start anything, backup your database.
BACKUP DATABASE [your db name]
TO DISK='c:\mydb.bak'
http://msdn2.microsoft.com/en-us/library/ms186865.aspx|||
i run
sp_helpindex N'IvMthEnd'
then i got the result as below
index name PK_IvMthEnd
index_description nonclustered located on PRIMARY
index_keys M_Period, M_Year, I_Cd, WHouse_Cd
then i drop the index with the code below
GO
DROP INDEX PK_IvMthEnd
ON IvMthEnd;
GO
Command(s) completed successfully.
then i create the index again with the code below
GO
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'PK_IvMthEnd')
DROP INDEX PK_IvMthEnd ON IvMthEnd;
GO
CREATE INDEX PK_IvMthEnd
ON IvMthEnd (M_Period, M_Year, I_Cd, WHouse_Cd);
GO
Command(s) completed successfully.
i still face the same problem, can not delete the record and get the same result possible index corrupted
i run the DBCC CHECKDB , no error message show.
|||Ah. You will want to drop the statistics as well.
exec sp_helpstats 'IvMthEnd'
go
drop statistics IvMthEnd.<statname>
go
|||
erm..
After i run exec sp_helpstats 'IvMthEnd'
go
i get the result like below
Statistics Name
_WA_Sys_Adj_Qty_0880433F
_WA_Sys_Avg_Iv_Cost_0880433F
_WA_Sys_Close_Bal_0880433F
_WA_Sys_I_Cd_0880433F
_WA_Sys_Issue_Qty_0880433F
_WA_Sys_Last_Update_0880433F
_WA_Sys_M_Year_0880433F
_WA_Sys_Open_Bal_0880433F
_WA_Sys_Receipt_Qty_0880433F
_WA_Sys_UOM_0880433F
_WA_Sys_User_ID_0880433F
_WA_Sys_WHouse_Cd_0880433F
then i go for
DROP INDEX PK_IvMthEnd
ON IvMthEnd;
GO
then i create the index again
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'PK_IvMthEnd')
DROP INDEX PK_IvMthEnd ON IvMthEnd;
GO
CREATE INDEX PK_IvMthEnd
ON IvMthEnd (M_Period, M_Year, I_Cd, WHouse_Cd);
GO
But i still facing the same problem.... i still can not delete the record in the SQL server management studio.
P/S : i would like to ask something about the command
sp_helpindex N'IvMthEnd'
What's the different between
clustered, unique, primary key located on PRIMARY and nonclustered located on PRIMARY
|||You need to run the "drop statistic lvMthEnd._WA_Sys_Adj_Qty_0880433F" etc.Dropping the index is not the same as dropping the stats.
clustered, unique, primary key on PRIMARY means you've created a clustered index on the key which resides on the primary filegroup. You can lookup for "clustered" and "nonclustered" in book online for more info.
|||
i have done drop the statistic and drop the index also ...
but still the same ...
so if i want to create back the clustered, unique, primary key , how to do it.
thanks.. ..
|||To create the clustered primary key, you can do something like this.alter table <tb_name>
add constraint <pk_name> primary key clustered (<col1, 2,...n>)
Now to your problem, something doesn't jive here. So, you deleted all indexes and statistics on the table and you still got the "index corruption" warning?
Perhaps, this is a case for MS support to closely work with you to track down the root cause.
No comments:
Post a Comment