1

I am currently running into the following intermittent error from my applications logs

Commit - ERROR: Code=0x80004005 Source=Microsoft OLE DB Provider for SQL Server
Description=Insert bulk failed due to a schema change of the target table.

I am unable to find out what DDL statement would be causing this and have found nothing in the SQLServer logs themselves. (I assumed offline indexing, but that is not happening)

My hypothesis is that the autogrow event is set too low and the intermittent problem is caused when it gets triggered.

Is this a valid hypothesis and is there a method of testing it?

4

As documented by By Parikshit Savjani in

Insert bulk failed due to a schema change of the target table:

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.

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.

There is no reason that auto growth of a file would cause a plan recompile.

I would suspect auto statistics update as the most likely option but the link does mention some other possibilities:

  • Parallel Bulk Inserts happening on the same table, and foreign keys are enabled on the table.

  • Parallel Online rebuild index operation running.

It also mentions that bulk inserting with the option to ignore constraints will bump the internal metadata version, which can lead to this error. The workaround is to drop the constraints before the bulk operation, then recreate them afterward.

You could turn off autostats for the table using norecompute for the duration of the operation and then update them afterwards.

  • I have come across this scenario and the culprit in my case was the index rebuild. – SQLPRODDBA Jun 7 '16 at 3:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.