Results 1 to 5 of 5

Thread: Timeout

  1. #1
    Join Date
    Nov 2002
    Posts
    84

    Question Timeout

    Hi,

    We are running an ASP Application with the backend SQL2000 Ent. Edition.

    A stored proc which was running good for a week suddenly gave problem and it was timing out in the front end. when it is running in the query analyser, it runs fine.

    We hav rebuild indexes on all tables running once in a week and index on frequently used table ( that has transactions often) daily. The Updatestats is also running daily.

    Now what happenned it we altered the stored proc and now there was no timeout. we don't know what is the reason and why it is causing that. we don't know whether it will cause the problem again.

    I would appreciate if you have any information on this type of problem or any bug information about SQL server 2000 SP2 that causes this type of problem and help me out.

    Thanks in advance,
    Anu

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Did you compare the query execution plan before and after altering the procedure?

    what was the change between previous procedure and now. Does this recompile everytime when you run this procedure (example set concat yeilds nulls off - recompiles the procedure everytime when you run)

  3. #3
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    BTW.

    Any change in the network after you implemented the stored procedure change? like new network card (with auto detect instead of FULL Duplex)

    Do they cache information in ASP before and now?

    Any change in machine name or ip address.

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Do you have

    SET NOCOUNT ON

    in your new sp?

  5. #5
    Join Date
    Nov 2002
    Posts
    84

    Timeout

    Thanks Guys,

    We atlast came to know that the stored proc which was timing out was the Victim and not the Culprit as it was running good after some processes completed. We are still analysing which one is causing this to timeout using the trace files.

    Thanks for your help guys.

    Anu

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •