Stored Proc Timeout When Running Simultaneously With Large BCP Insert

• mark.dempster

  Grasshopper

  Points: 24

  More actions

  October 24, 2007 at 2:12 am

  #199345

  I have a server running SQL 2000 running on a win2003 cluster, it has two databases;

  1) On DB1, I have a stored proc that simply adds a single record into a table (primary key is an 'identity' column). The stored proc is called from a classic ASP application using ADO, it inserts on average 100 records per hour (every hour) and take milliseconds to execute.

  2) On DB2, I have a large BCP routine (4gb file, 5 million records, with a BCP batch size of 100000) inserting records into a single table. This routine is run once a day and takes approx 1 hour to execute.

  The problem I have is that my stored proc on DB1 will occasionally timeout (average of 5% of the time) when it is executed at the same time as the BCP routine is executing. When the BCP stops running the stored proc never experiences any timeouts.

  NOTE: The stored proc and BCP routines are independent of each other, the only thing they share in common are they both operate on the same instance of SQL server.

  Can anyone suggest why this is happening?