Executing BCP fails with SQLState = 37000, NativeError = 4891. Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Insert bulk failed due to a schema change of the target table

BCP is a very commonly used operation and sometime very critical and failure could cause loss of operation time and inconsistent data. Below is one of the cause resulting in bcp operation failure and the error indicates some schema change issue. here is more about it.

Error
====

The BCP fails with the below error.

SQLState = 37000, NativeError = 4891
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Insert bulk failed due to a schema change of the target table.
BCP copy in failed

This error says that there have been changes in the schema that require the re-compilation of the plan and Insert bulk cannot support recompiles. This looks like a timing issue having to do with bcp sending row data to the server with an out-of-date schema due to schema level changes happening due to other SQL Server operation like Auto Update Statistics or Parallel execution on the same target table.

Any Operation on the Target tables which would require the recompilation of the query will cause this error messages.

In a normal scenario, the Schema Change could be addressed by a re-compile, however it turns out that for BCP re-compiles are not possible because at compile time, we are reading metadata from the input stream and we set up our statement based on that, and then basically discard that metadata. If we recompile, that metadata is completely lost, and compilation is no longer possible.

Below are few(among many) of the causes, why the BCP fails with the above error as recompile is required.

1) Parallel Bulk Inserts happening on the Same table and foreign keys are enabled on the table. This could lead to the deadlock.

2) Parallel Online rebuild index operation running.

3) Auto update stats.

In fact the problem comes from an optimization done for the constraints checking:

https://msdn.microsoft.com/en-us/library/ms186247(SQL.90).aspx

When constraints are disabled, a schema modify lock might be taken to update the metadata. But, this can interfere with other commands (such as an online index build) or transactions. For example, a snapshot isolation transaction accessing the target table might fail due to concurrent DDL changes.

Below is some of the action plan you can try, this helped in my case though.

1) Drop the Constraints before the BCP run and recreate them after the run

2) Disable the Auto update stats (To isolate the issue)

3) Check if any parallel index rebuilds happening.

If still the issue persists after implementing the above change, collect the Profiler trace to capture the activity when bcp is failing to further investigation.

Check the following link which explains about the auto update stats functionality and the locks applied. :

https://support.microsoft.com/kb/195565

Sandeep Dasam
SE, Microsoft SQL Server

Reviewed by:
Nickson Dicson
Tech lead, Microsoft SQL Server