Article ID: 260742
Article Last Modified on 12/30/2004
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q260742
BUG #: 57791 (SQLBUG_70)
SYMPTOMS
DBCC DBREINDEX may cause system table referential integrity problems (a 2513 error message), and allow you to drop a referenced primary key constraint, if the following conditions are met:
- A clustered index exists on the first table.
- A non-clustered index or statistic exists on the first table.
- A non-clustered primary key is created on the first table such that it has a greater index identifier (indid) than the index or statistic created in the second item of this list.
- A foreign key exists on the second table that references the primary key of the first table.
- The non-clustered index or statistic created in the second item of this list is dropped.
- DBCC DBREINDEX is run on the first table.
NOTE:
- It is possible to drop the primary key constraint on the first table, even if it is referenced by the foreign key constraint on the second table.
- A delete on the first table may cause a handled Access Violation (AV).
- If you run DBCC CHECKCATALOG against this database, the 2513 error message occurs.
Note Sometimes, DBCC CHECKDB, DBCC CHECKALLOC, and DBCC NEWALLOC statements may not report corruption even though the same problem exists.
CAUSE
The indid of the nonclustered primary key index has changed, and this change is not reflected in the sysreferences system table.
WORKAROUND
Update the sysreferences system table so that the index id of the nonclustered index of the primary key is now the correct one. You must also update the rkeyindid of the sysreferences system table.
Please note that applying the service pack will prevent the problem from being created again. However, you will continue to get the access violations, even on SP3, until the above steps are implemented.
STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For additional information about how to download and install the latest SQL Server service pack, click the following article number to view the article in the Microsoft Knowledge Base:
301511 How to obtain the latest SQL Server 7.0 service pack
For more information, contact your primary support provider.
MORE INFORMATION
To reproduce this problem, run the following script:
drop table y
go
drop table x
go
create table x( a int not null, b int not null, c int not null)
go
create clustered index xcidx on x(a)
go
create statistics xstat on x(c)
go
alter table x add constraint xpk primary key nonclustered (b)
go
create table y (b int not null, c int not null)
go
alter table y add constraint yfk foreign key (b) references x(b)
go
select * from sysindexes where id = object_id('x')
select * from sysreferences where fkeyid = object_id('y') and rkeyid = object_id('x')
go
drop statistics x.xstat
go
dbcc dbreindex('x')
go
print ' '
print 'sysreferences now out of sync'
print ' '
go
select * from sysindexes where id = object_id('x')
select * from sysreferences where fkeyid = object_id('y') and rkeyid = object_id('x')
go
In the following code, which reproduces the AV, trace flag 8790 is used to force an Optimizer path that can occur when there is a lot of data in the table:
dbcc traceon(8790)
go
delete x
go
dbcc traceoff(8790)
go
Here is the Short Stack Dump from the AV:
Short Stack Dump
0x006a1f6a Module(sqlservr+2a1f6a) (CUpdUtil::PMapGenerateIndexMappings+ef)
0x006a557a Module(sqlservr+2a557a) (CUpdUtil::PexprBuildWideDelPlan+249)
0x005875e2 Module(sqlservr+1875e2) (CSubRuleExpandDeleteCons::BuildSubstitutes+107)
0x004716a7 Module(sqlservr+716a7) (CTask_ApplyRule::Perform+253)
0x004702d7 Module(sqlservr+702d7) (CMemo::ExecuteTasks+144)
0x00470023 Module(sqlservr+70023) (CMemo::OptimizeQuery+480)
0x0046daf1 Module(sqlservr+6daf1) (COptContext::PexprSearchPlan+35f)
0x0044b850 Module(sqlservr+4b850) (COptContext::PcxteOptimizeQuery+7ea)
0x0044afe4 Module(sqlservr+4afe4) (CQuery::Optimize+206)
0x0044ab70 Module(sqlservr+4ab70) (CQuery::Prepare+91)
0x00445f4b Module(sqlservr+45f4b) (CCvtTree::PqryFromTree+7eb)
0x00445c74 Module(sqlservr+45c74) (BuildQueryFromTree+60)
0x00445b13 Module(sqlservr+45b13) (CStmtQuery::InitQuery+c8)
0x0044e40a Module(sqlservr+4e40a) (CStmtDML::InitNormal+da)
0x0044e384 Module(sqlservr+4e384) (CStmtDML::Init+32)
0x004367f6 Module(sqlservr+367f6) (CCompPlan::FCompileStep+8a3)
0x0043b503 Module(sqlservr+3b503) (CProchdr::FCompile+5d7)
0x00413dbd Module(sqlservr+13dbd) (CSQLSource::FTransform+233)
0x00480033 Module(sqlservr+80033) (CSQLStrings::FTransform+158)
0x00482161 Module(sqlservr+82161) (CSQLStrings::Compile+3e)
0x00482055 Module(sqlservr+82055) (CStmtPrepQuery::CStmtPrepQuery+2a1)
0x00436469 Module(sqlservr+36469) (CCompPlan::FCompileStep+110)
0x0043b503 Module(sqlservr+3b503) (CProchdr::FCompile+5d7)
0x00413dbd Module(sqlservr+13dbd) (CSQLSource::FTransform+233)
0x00480033 Module(sqlservr+80033) (CSQLStrings::FTransform+158)
0x00413923 Module(sqlservr+13923) (CSQLSource::Execute+11c)
0x0047fd57 Module(sqlservr+7fd57) (language_exec+396)
0x41061253 Module(opends60+1253) (execute_event+658)
0x410614a1 Module(opends60+14a1) (process_commands+f2)
0x41092a47 Module(ums+2a47) (ProcessWorkRequests+ec)
0x4109326b Module(ums+326b) (ThreadStartRoutine+138)
0x7800bea1 Module(MSVCRT+bea1) (beginthread+ce)
0x77e92ca8 Module(KERNEL32+12ca8) (CreateFileA+11b)
Keywords: kbbug kbfix kbcodesnippet KB260742
