When you copy in to a table using bcp, and particularly when you copy in to a table using parallel bcp, the copy process acquires the following locks:
An exclusive intent lock on the table
An exclusive page lock on each data page or data row
An exclusive lock on index pages, if any indexes exist
If you are copying in very large tables, and especially if you are using simultaneous copies into a partitioned table, this can involve a very large number of locks.
To avoid running out of locks, increase the number of locks.
To estimate the number of locks needed, use:
# of simultaneous batches * (rows_per_batch / (2016/row_length))
To see the row length for a table, use:
1> select maxlen
2> from sysindexes
3> where id = object_id("tablename") and (indid = 0 or indid = 1)
See the System Administration Guide for more information about setting the number of locks.
Use the -b batchsize flag to copy smaller batches; the default batch size is 1000 rows. The smallest batch size bcp can process is 1; the largest is 2147483647L.
Run fewer batches concurrently.
