Microsoft KB Archive/317852

= FIX: An INSERT May Fail with a 3624 Error Message After a Failed Attempt to Add New Table Column =

Article ID: 317852

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q317852





SYMPTOMS
A clustered index may be corrupted if all of the following conditions are met:

 You try to add a column to an existing table and in the same transaction you add a foreign key reference from the new column to a column that is a unique clustered or unique nonclustered index key on the same table.

 The add column fails and the transaction is rolled back.

After the transaction fails and is rolled back, an INSERT statement to the same table may result in an error message similar to one of the following:

Server: Msg 3624, Level 20, State 1, Line 1

Location: recbase.cpp:1378

Expression: m_offBeginVar < m_SizeRec

SPID: 51

Process ID: 948

Connection Broken

-or-

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData).

Server: Msg 11, Level 16, State 1, Line 0

General network error. Check your network documentation.

Connection Broken

Additionally, if you try to reindex the table by using a DBCC DBREINDEX statement, the following error message occurs:

Server: Msg 2727, Level 16, State 48, Line 1

Cannot find index ''.





CAUSE
The clustered index of the table is corrupted after the failed add column attempt. DBCC CHECKDB does not detect or report this particular scenario.



RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How To Obtain the Latest SQL Server 2000 Service Pack



WORKAROUND
To work around this problem you can either:  Drop, and then re-create the index.

-or-  Use this statement to re-create the corrupted index: CREATE CLUSTERED INDEX...WITH DROP_EXISTING </li></ul>

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 3.

<div class="moreinformation_section">

MORE INFORMATION
To reproduce the problem, run the following script from Query Analyzer against the pubs sample database: BEGIN TRAN ALTER TABLE jobs ADD myfield SMALLINT ALTER TABLE jobs ADD CONSTRAINT fk_myfield FOREIGN KEY (myfield) REFERENCES jobs (job_id) ROLLBACK TRAN The script completes successfully. However, an INSERT or DBCC DBREINDEX statement on the jobs table fails with the errors listed in the &quot;Symptoms&quot; section of this article.

You can also look for the following stack dump and assertion in the SQL Server error log if you encounter this problem. The following example stack dump is from SQL Server 2000 Service Pack 2:

<pre class="fixed_text">* Short Stack Dump (CStackDump::GetContextAndDump+0000002E) (CStmtQuery::ErsqExecuteQuery+00000357) 2002-02-05 18:19:13.05 spid57   SQL Server Assertion: File: <recbase.cpp>, line=1378 Failed Assertion = 'm_offBeginVar < m_SizeRec'. 2002-02-05 18:19:13.09 spid57   Error: 3624, Severity: 20, State: 1.
 * 0086CD4C Module(sqlservr+0046CD4C)
 * 0086E778 Module(sqlservr+0046E778) (stackTrace+0000021D)
 * 00923F7F Module(sqlservr+00523F7F) (utassert_fail+000002E3)
 * 007EDE18 Module(sqlservr+003EDE18) (RecBase::ReSizeVar+00000069)
 * 00401F19 Module(sqlservr+00001F19) (RecBase::Resize+0000005B)
 * 0040269C Module(sqlservr+0000269C) (RecBase::Size+0000000E)
 * 0042710F Module(sqlservr+0002710F) (cinsert+00000025)
 * 004175DC Module(sqlservr+000175DC) (rowinsert+00000101)
 * 0041B77F Module(sqlservr+0001B77F) (insert+00000013)
 * 004237A2 Module(sqlservr+000237A2) (RowsetSS::InsertRow+000001F4)
 * 0041AF92 Module(sqlservr+0001AF92) (CValRow::SetDataX+00000026)
 * 0041A2A4 Module(sqlservr+0001A2A4) (SetDataWithPop+0000001C)
 * 0041CBE4 Module(sqlservr+0001CBE4) (CEs::GeneralEval+000000DC)
 * 006EDB92 Module(sqlservr+002EDB92) (CQScanUpdate::GetRow+000001DA)
 * 00483AC2 Module(sqlservr+00083AC2) (CQScanAssert::GetRow+0000001B)
 * 00419D7F Module(sqlservr+00019D7F) (CQueryScan::GetRow+00000014)
 * 004191CC Module(sqlservr+000191CC)
 * 0041AA7B Module(sqlservr+0001AA7B) (CStmtDML::XretExecuteNormal+000002AE)
 * 0041A8A9 Module(sqlservr+0001A8A9) (CStmtDML::XretExecute+0000001C)
 * 0040F403 Module(sqlservr+0000F403) (CMsqlExecContext::ExecuteStmts+000002D9)
 * 0040EA95 Module(sqlservr+0000EA95) (CMsqlExecContext::Execute+000001B6)
 * 00410159 Module(sqlservr+00010159) (CSQLSource::Execute+00000331)
 * 0053EDBE Module(sqlservr+0013EDBE) (CStmtPrepQuery::XretExecute+00000200)
 * 0040F403 Module(sqlservr+0000F403) (CMsqlExecContext::ExecuteStmts+000002D9)
 * 0040EA95 Module(sqlservr+0000EA95) (CMsqlExecContext::Execute+000001B6)
 * 00410159 Module(sqlservr+00010159) (CSQLSource::Execute+00000331)
 * 0053C498 Module(sqlservr+0013C498) (language_exec+000003E1)
 * 00411099 Module(sqlservr+00011099) (process_commands+000000EC)
 * 41073379 Module(UMS+00003379) (ProcessWorkRequests+0000024A)
 * 41073071 Module(UMS+00003071) (ThreadStartRoutine+000000BD)
 * 7800C9EB Module(MSVCRT+0000C9EB) (beginthread+000000CE)
 * 77E96523 Module(KERNEL32+00016523) (TlsSetValue+00000115)

Keywords: kbfix kbprb KB317852

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.