Assertion or MDF Corruption When You Import Data in SQL Server 2000

You may face database corruption or receive an assertion in SQL Server 2000 when you use the “Bulk Insert” T-SQL (Transaction Structured Query Language) command or BCP (Bulk Copy Program) utility to import data. In such situations, data from SQL Server MDF files can become inaccessible. SQL Database Recovery is required to overcome such issues.

BCP is a command line utility in SQL Server that is used in a script or batch file to automate the import and export processes through text files. To import data in a table, you must either understand the structure of table and types of data or use a format file designed for that table.

When you use BCP command line utility or Bulk Insert T-SQL command for importing data, MDF corruption may occur and you may get the following error message:

“Server: Msg 823, Level 24, State 2, Procedure gfspTMAppendText, Line 20 I/O error (bad page ID) detected during read at offset 0x0000000024e000 in file 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL\data\'.”

You receive following information in error log of SQL Server 2000:

1.“DateTime spid53 SQL Server Assertion: File: , line=466 Failed Assertion = '0'.”
2.“DateTime spid53 Error: 3624, Severity: 20, State: 1.”

If you run DBCC (Database Consistency Checker) command like DBCC CHECKDB command to find and repair corruption in the database, you receive further error messages stating:

1.“Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID 0, page ID (1:105). The PageId in the page header = (0:0).”
2.“Server: Msg 8928, Level 16, State 1, Line 1 Object ID 2009058193, index ID 255: Page (1:105) could not be processed. See other errors for details.”

Such SQL Server behavior causes critical data loss. You should figure out the cause to Repair SQL database and gain access of your lost data.

Root of this issue

You may experience assertion and MDF corruption problems, if following conditions are true:

You use Bulk Insert or BCP utility together with the FIRE_TRIGGERS option.
You import a database table which has an after trigger that updates the text column of another database table.

Resolution

You can fix database corruption with the help of SQL Database Recovery software. These are highly automated tools that completely scan the corrupted MDF file and extract all of its objects as tables, stored procedures, indexes, views, triggers, constraints etc.

SQL Recovery is the most advanced SQL Database Recovery tool. It can Repair SQL database components like tables, views, rules, stored procedures, defaults, user defined functions and data types along with triggers. It is compatible with Microsoft SQL Server 2005 and 2000. It supports Microsoft Windows Vista, XP, 2003 and 2000.