bcp in and Locks

When you copy in to a table using bcp—particularly when you copy in to a table using parallel bcp—the copy process acquires an exclusive lock.

  • 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—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.