(Strange?) Timeout Expired problem

Question

(Strange?) Timeout Expired problem

DubNoBass

SSC Rookie

Points: 27
More actions
April 17, 2003 at 8:39 am

#80069

Hi,
Perhaps there is a simple answer to this one:
I have a very simple stored procedure that selects rows from a table (with very few rows in it), selecting on a clustered index.
I am using ODBC to connect to the SQL Server 7.0 Database.
Every now and then, the customer is reporting a 'Timeout Expired' problem on this SP. Using the Profiler, and tracking RPC and SP events, I see a difference between when the call is successful (i.e. doesn't time out) and when it fails (i.e. 'Timeout Expired'):
When it succeeds, I see the RPC call starting, SP calls starting and completing (very quickly I might add!) and then the RPC call completing.
When it fails, I see the RPC call starting and very soon after (well within 30 seconds) the RPC call completes, with no SP events inbetween! Like the stored procedure didn't do anything! This is when the client then times out.
Any one else seen this, or have any ideas? What other data should I collect/examine to get to the bottom of this?
DubNoBass.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

vambati SSCrazy Points: 2769 More actions · Answer 1

You can try to run execution plan to see what it does, though u say sometimes it timesout. Try to get TSQL event in profiler and SP events. That should shed some light on why its happening.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 2

Do you have timeout by running same sp from QA? Have you checked whether there was blocking when it happened?

DubNoBass SSC Rookie Points: 27 More actions · Answer 3

Thanks guys - When I run the execution plan on the SP, it tells me it is going to use a clustered index scan - which sounds OK to me. There really isn;t a lot of data in the table anyway (~50 rows).

I have monitored TSQL and SP events in the Profiler - but when it fails I don't see any of these events - I just see an RPC started and RPC completed event, which seems strange...

I have not recreated it through QA, as it is hard to reproduce. I am not sure there is a blocking problem (of data) as I do not see the Profiler hanging on a TSQL statement - Is there another way I can prove it is not blocking?