Microsoft KB Archive/815594

= BUG: A Failed Assertion Is Generated During a BULK INSERT Statement =

Article ID: 815594

Article Last Modified on 9/22/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-





SYMPTOMS
SQL Server may cause the following failed retail assertion during a process that involves frequent, cyclic BULK INSERT statements followed by DELETE statements. To generate the assert, the BULK INSERT activity must be operating in nonlogged mode:  2003-02-18 20:59:45.83 spid54    SQL Server Assertion: File: , line=3721 Failed Assertion = '(logMode != nonlogged) || (dbt->dbt_dbid == TEMPDBID)'. Note To operate in nonlogged mode, use the TABLOCK option.

A call stack similar to the following is also generated in the SQL Server error log: * --- Additionally, after this type of process, if you then use DELETE operations on the BULK INSERT destination table, the DBCC CHECKDB and DBCC CHECKTABLE Transact-SQL references may report the following errors:
 * Short Stack Dump
 * 0087A90E Module(sqlservr+0047A90E) (CStackDump::GetContextAndDump+0000002E)
 * 0087C41F Module(sqlservr+0047C41F) (stackTrace+00000223)
 * 00931097 Module(sqlservr+00531097) (utassert_fail+000002E3)
 * 008075E4 Module(sqlservr+004075E4) (ChangeGhostPageState+000000D5)
 * 00808855 Module(sqlservr+00408855) (DataSplitMgr::SplitPage+000000F4)
 * 00572C15 Module(sqlservr+00172C15) (indexsplit+00000068)
 * 00572AE7 Module(sqlservr+00172AE7) (SplitIndexNode+0000003D)
 * 00406CEC Module(sqlservr+00006CEC) (srchindex+000002C1)
 * 004372AF Module(sqlservr+000372AF) (InsertRowIntoNCIndex+0000018A)
 * 0080121A Module(sqlservr+0040121A) (RowsetSS::InsertRow+000000D1)
 * 0040E01C Module(sqlservr+0000E01C) (CValRow::SetDataX+00000035)
 * 0040AE83 Module(sqlservr+0000AE83) (SetDataWithPop+0000001C)
 * 0041C5BE Module(sqlservr+0001C5BE) (CEs::GeneralEval4+00000075)
 * 00486561 Module(sqlservr+00086561) (CQScanUpdate::GetRow+000001DA)
 * 006F9B06 Module(sqlservr+002F9B06) (CQScanSequence::Open+000000DA)
 * 004214C4 Module(sqlservr+000214C4) (CQueryScan::Startup+0000010D)
 * 0041D505 Module(sqlservr+0001D505) (CStmtQuery::ErsqExecuteQuery+0000026B)
 * 0041E09C Module(sqlservr+0001E09C) (CStmtDML::XretExecuteNormal+000002AE)
 * 0041DECA Module(sqlservr+0001DECA) (CStmtDML::XretExecute+0000001C)
 * 0041B442 Module(sqlservr+0001B442) (CMsqlExecContext::ExecuteStmts+000003B9)
 * 0041AA88 Module(sqlservr+0001AA88) (CMsqlExecContext::Execute+000001B6)
 * 0041B9B6 Module(sqlservr+0001B9B6) (CSQLSource::Execute+00000357)
 * 0047F78C Module(sqlservr+0007F78C) (CSQLSource::SeExecute+00000051)
 * 0047F6DC Module(sqlservr+0007F6DC) (ExecSql+000000EA)
 * 0047F590 Module(sqlservr+0007F590) (CBcpImport::ExecInsertStmt+00000447)
 * 0047D869 Module(sqlservr+0007D869) (BcpImportMain+00000196)
 * 0047D714 Module(sqlservr+0007D714) (CStmtBulkIns::XretExecute+0000005F)
 * 0041B442 Module(sqlservr+0001B442) (CMsqlExecContext::ExecuteStmts+000003B9)
 * 0041AA88 Module(sqlservr+0001AA88) (CMsqlExecContext::Execute+000001B6)
 * 0041B9B6 Module(sqlservr+0001B9B6) (CSQLSource::Execute+00000357)
 * 0048A63B Module(sqlservr+0008A63B) (language_exec+000003E1)
 * 0042708C Module(sqlservr+0002708C) (process_commands+0000010E)
 * 41075002 Module(UMS+00005002) (ProcessWorkRequests+00000272)
 * 41074698 Module(UMS+00004698) (ThreadStartRoutine+00000098)
 * 7800A27B Module(MSVCRT+0000A27B) (beginthread+000000CE)
 * 77E8758A Module(KERNEL32+0000758A) (SetFilePointer+0000018A)

Server: Msg 8952, Level 16, State 1, Line 1

Table error: Database 'BulkTest', index 'FilterKeys.IX_FilterKeys_FileID' (ID 2057058364) (index ID 2). Extra or invalid key for the keys:

Server: Msg 8956, Level 16, State 1, Line 1

Index row (3:12:59) with values (FileID = '{B08ED36E-EADF-47DF-B426-A793C683BB4A}') points to the data row identified by (RID = (3:46405:14)).



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



CAUSE
This error occurs when a BULK INSERT operation triggers a page split on an index node that results in the newly-created index page containing &quot;ghost records.&quot; The process that causes the controlling Page Free Space (PFS) page of the new page to be marked as containing ghosted records should not occur during a nonlogged operation.



WORKAROUND
Because this problem only occurs on a heap (or a SQL Server table that has no clustered index), put a clustered index on the BULK INSERT destination table to work around this problem.



MORE INFORMATION
SQL Server uses ghosted records as a concurrency optimization to DELETE operations. When a row is deleted (or moved) from an index leaf page, the space is not immediately released to SQL Server. Instead, it is marked as a &quot;ghost.&quot; A SQL Server system process (Ghost Record Cleanup) operates in the background to remove these records asynchronously.

Keywords: kbprb KB815594

-

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

© Microsoft Corporation. All rights reserved.