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


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
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

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.

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 "Symptoms" 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:

* Short Stack Dump
* 0086CD4C Module(sqlservr+0046CD4C) 
(CStackDump::GetContextAndDump+0000002E)
* 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) 
(CStmtQuery::ErsqExecuteQuery+00000357)
* 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)
* -------------------------------------------------------------------------------
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.
Modification Type:MajorLast Reviewed:10/3/2003
Keywords:kbfix kbprb KB317852 kbAudDeveloper
©2003 Microsoft Corporation. All rights reserved.