CODEWITHSUNDEEP
sql-serverc#-4.0
Leon Huang
Leon Huang

Reputation: 103

SQL Server suspended

I have two applications that do something to the same SQL Server table. One application uses C# SqlBulkCopy to import about two hundred thousand records into the SQL Server table, and the other application queries data from the same SQL Server table.

I find this message - please check the screenshot. The table has one hundred million rows. How can I fix it?

Please check this picture

Upvotes: 0

Views: 130

Answers (1)

marc_s
marc_s

Reputation: 754973

If any transaction is modifying a table and affecting more than 5000 rows, then SQL Server will escalate the locking from row-level locking to an exclusive table lock.

So if your application #1 is bulk-loading 200'000 rows into the table, then that table will be exclusively locked for the duration of the loading process.

Therefore, your application #2 - or any other client - won't be able to query that table, until the loading process is done.

This is normal, documented, expected behavior on the part of SQL Server.

Either make sure you load your data in batches of less than 5000 rows at a time during business hours, or then do the bulk-loading after hours, when no one is being negatively impacted by an exclusive table lock.

Upvotes: 1

Related Questions