SQL Server assertion error when you try to execute a Bulk Insert or BCP statement
This article helps you resolve the problem that occurs when you try to execute a BULK INSERT
or BCP
operation.
Original product version: SQL Server 2008 R2 Enterprise, SQL Server 2008 Enterprise
Original KB number: 2700641
Symptoms
Consider the following scenario:
Server A and Server B are running Microsoft SQL Server 2008 or SQL Server 2008 R2.
You set up database mirroring between Server A and Server B.
You execute a
BULK INSERT
orBCP
statement on the principal database.Note
By default, the
CHECK_CONSTRAINTS
option is set to off when you execute aBULK INSERT
orBCP
statement.The database mirroring is broken, and the database mirroring session enters the SUSPENDED state.
In this scenario, an assertion occurs on the mirror server. Therefore, a mini-dump file is created in the SQL Server log folder. Additionally, you see following error in the SQL Server error log on the mirror server:
Note
You must reinitialize database mirroring to resolve this issue.
Cause
This issue occurs because the lock compatibility information in the transaction log of the principal database isn't transferred to the mirror server.
Workaround
To work around this issue, execute the BULK INSERT
or BCP
statement on the principal database by using the CHECK_CONSTRAINTS
option.
Note
The CHECK_CONSTRAINTS
option causes slower performance. However, the lock assert on the mirror server doesn't occur.
More information
During a BULK INSERT
or BCP
operation, a child transaction turns off the CHECK_CONSTRAINTS
option. This child transaction uses a lock that is compatible with the parent transaction locks. The compatibility information is stored in the transaction log of the principal database. Therefore, the child transaction lock request is granted on the principal database.
However, this compatibility information isn't transferred to the mirror server. Therefore, the child transaction lock request is incompatible with the parent transaction locks on the mirror server. This scenario causes the assert on the mirror server.
Feedback
https://aka.ms/ContentUserFeedback.
În curând: Pe parcursul anului 2024, vom elimina treptat Probleme legate de GitHub ca mecanism de feedback pentru conținut și îl vom înlocui cu un nou sistem de feedback. Pentru mai multe informații, consultați:Trimiteți și vizualizați feedback pentru