Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I ran SQL Server 2005's bulk copy program BCP like this:

bcp mydb.dbo.mytbl in myfile.blk -c -S mysvr -U mylogin -P mypass

And it ran and produced output like so:

Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
...
1000 rows sent to SQL Server. Total sent: 55000

But then it stopped. The cursor did not return to prompt, I haven't received the "... rows copied." message.

I tried querying the target table and I can already see the rows that I wanted to import.

Shall I terminate the console? Will it rollback?

share|improve this question

2 Answers 2

After 3 hours, I finally got my prompt back:

55837 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 10582641 Average : (5.28 rows per sec.)

Although I still don't know why it didn't return immediately since the rows were already inserted. My best guess was it did index rebuilding.

I will try terminating the console the next time it hangs up like that.

share|improve this answer

The process appears to hang because, although it has sent all the data to SQL Server, the data has only been placed in sort buffers - it has not yet reached the destination table.

When the table has indexes, SQL Server will sort the data into the required index order before inserting. If the data set is large, there are many indexes, or SQL Server has insufficient sort memory available, this process can take significant time. The presence of indexes and existing table data can also affect the ability of SQL Server to use optimized minimally-logged inserts.

Without minimally-logged inserts, the insertion process (after sorting) will also be slow because each row is fully recorded in the transaction log (including the information needed to undo the insert to ensure recoverability).

Several bcp options can be specified to help achieve minimally-logged inserts. Other measures may also be necessary, such as temporarily changing the recovery model of the database to support efficient bulk operations. Dropping nonclustered indexes before the insert and rebuilding them afterwards is also often the optimal strategy.

To summarize the main points of a complex topic:

  • Use a recovery model that supports minimally-logged inserts
  • Specify a TABLOCK hint (exact syntax varies depending on insert method)
  • Specify the ORDER hint and ensure the data source is pre-sorted by the clustering key
  • Disable triggers and constraints
  • Load into an empty table if possible

For more information, see:

Optimizing Bulk Insert Performance and linked pages

For SQL Server 2008 and later:

The Data Loading Performance Guide

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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