Re: Stored proc timeout issue
- From: "Jeremy" <jeremy-nospam@xxxxxxxxxxxxxx>
- Date: Sun, 28 Oct 2007 10:14:33 -0700
I have a stored proc that deletes certain records in half a dozen tables,
one table after another, in a D7 app. The sp creates a temp table at the
beginning with IDs of the records to be deleted. Ado, ms sql server 2k5.
The program Opens the proc and times out after 2 minutes. When I run the
proc from sql server mgmt studio, it completes in 11 seconds.
This suddenly started working, with no apparent reason. I very carefully reviewed the sql server Activity Monitor while I was working and I'm pretty sure no one else has been using the database this morning.
This morning I ran the program function that is the problem, and it timed out (120 seconds). I began diagnosing the problem by commenting out various lines of code in the stored proc (child table deletes, mostly). Each time, I ran the program and it succeeded, and I added back in a few lines. Eventually they were all back in, and the program succeeded. One thing that remained changed was the command timeout value. The program sets the timeout to 120 seconds. Since I was certain the proc would not run that long, I commented out this line during my diagnostic testing.
I have to reiterate in case someone is wondering, using the activity monitor, I checked for blocking, refreshing the display frequently during the 120 second lockup, during the time when the problem existed.
Is it possible that there's a bug in sql 2k5, or could there be corruption in the database? How would I know?
Jeremy
.
- References:
- Stored proc timeout issue
- From: Jeremy
- Stored proc timeout issue
- Prev by Date: Inserting and accessig Image (bmp) data in an SQL 2005 database.
- Next by Date: Re: Formatting output of TDBEdit
- Previous by thread: Stored proc timeout issue
- Next by thread: Inserting and accessig Image (bmp) data in an SQL 2005 database.
- Index(es):
Relevant Pages
|
