Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Stored Proc Timeout When Running Simultaneously With Large BCP Insert Expand / Collapse
Posted Wednesday, October 24, 2007 2:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2008 6:11 AM
Points: 2, Visits: 4
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?
Post #414247
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse