Article ID: 311120 - Last Review: September 26, 2005 - Revision: 4.2

FIX: Abort of Bulk Insert May Result in Error Message 8979 from CHECKDB

View products that this article applies to.
System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.
This article was previously published under Q311120
BUG #: 355898 (SHILOH_BUGS)
Expand all | Collapse all

SYMPTOMS

If you abort a non-logged bulk insert (bcp [bulk-copy] or Transact-SQL bulk insert) against an empty table, the following error message may occur when you run a DBCC CHECKDB statement or DBCC CHECKTABLE statement:
Server: Msg 8979, Level 16, State 1, Line 1
Table error: Object ID 1977058079, index ID 1. Page (1:78) is missing references from parent (unknown) and previous (page (0:0)) nodes. Possible bad root entry in sysindexes.
Back to the top

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  (http://support.microsoft.com/kb/290211/EN-US/ ) INF: How To Obtain the Latest SQL Server 2000 Service Pack
NOTE: The following hotfix was created before the release of Microsoft SQL Server 2000 Service Pack 3.

The English version of this fix should have the following file attributes or later: 
   Version      File name        Comments
   ---------------------------------------------
   8.00.459     Sqlservr.exe     SP1-based fix
   8.00.552     Sqlservr.exe     SP2-based fix
NOTE: Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.


Back to the top

WORKAROUND

To work around this problem you can either:
  • Use a TRUNCATE TABLE statement instead of DELETE to remove all rows in the table.

    -or-

  • Insert a row into the table before you execute the bulk insert, and then later delete this "dummy" row.
Back to the top

STATUS

Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000.
This problem was first corrected in Microsoft SQL Server 2000 Service Pack 3.
Back to the top

MORE INFORMATION

This problem only occurs if you:
  1. Insert rows into a table.
  2. Delete all the rows by using the DELETE statement (leaving an empty table with one [1] database page)
  3. Attempt to bulk-insert rows into the table.
If this problem occurs, you can correct it by:
  • Rebuilding the clustered index.

    -or-

  • Using the WITH REPAIR option of the DBCC CHECKDB statement or DBCC CHECKTABLE statement.
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
320434  (http://support.microsoft.com/kb/320434/EN-US/ ) FIX: Bulk Insert with TABLOCK Hint May Result in Errors 8929 and 8965 When You Run CHECKDB
Back to the top
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Back to the top
Keywords: 
kbhotfixserver kbqfe kbsqlserv2000sp3fix kbbug kbfix kbsqlserv2000presp3fix KB311120
Back to the top