Stored Proc "Timeout" When Running Simultaneously With Large BCP Insert

• mark.dempster

  SSC Rookie

  Points: 44

  More actions

  October 24, 2007 at 2:19 am

  #183078

  I have one server running SQL 2000, 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 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?

• ChiragNS

  One Orange Chip

  Points: 26137

  More actions

  October 24, 2007 at 2:55 am

  #744813

  Hi

  Just a guess...

  Maybe sql is running out of memory or the cpu is being heavily used by the BCP statement.

  "Keep Trying"