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

View products that this article applies to.

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: <access.c>, 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:

* -------------------------------------------------------------------------------
* 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)

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:

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)).

Back to the top

STATUS

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

Back to the top

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 "ghost records." 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.

Back to the top

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.

Back to the top

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 "ghost." A SQL Server system process (Ghost Record Cleanup) operates in the background to remove these records asynchronously.

Back to the top