Post reply

TimeOut Error

  • May 26, 2004 at 8:21 am

    #87496

    Hi

    I am getting Microsoft ODBC SQL server driver time out error on my ASP scripts, Its happening random.

    I am using

    ASP Scripts,

    SQL Sever 2000,

    Windows 2000 server,

    MDAC 2.6

    Any solution or suggestion?

    TIA

    -sn

  • May 26, 2004 at 10:29 am

    #507887

    Run profiler against your database, specify a duration and check those queries that are causing the problems.


    Shamless self promotion - read my blog http://sirsql.net

  • May 26, 2004 at 10:47 am

    #507890

    Its happening random...  This timeout error happens at SQL Server level or ASP script level?

     

  • May 26, 2004 at 10:56 am

    #507891

    You're asp is probably timing out when trying to execute something against the sql server. Check your server timeout options within the global.asa or the connection string on your asp.

    Check the statements that the asp are trying to execute, run an execution plan, check to see it doesn't show anything crazy. Check cpu, disk queueing, paging, memory on the sql server. Check all processes running on the sql server.

    That should narrow it down some.


    Shamless self promotion - read my blog http://sirsql.net

  • May 26, 2004 at 12:23 pm

    #507900

    This error happening not in particular sql call (most of the time it occurs on Insert statements)...  I added more error trapping script in my asp as well sql profiler on error.. 

    Is there any particular reason causes this error?

     

  • May 27, 2004 at 3:23 am

    #507967

    Please read this carefully:

    When you want a report which based on complicated queries joined from several tables, These tables are

    updated frequently, then you lock these tables (or you will wait for previous transaction lock on these tables

    to be completed to put your locks on them) .. so all other poeple using these tables are locked too (so hang

    happened - because they are in the queue of lock of these tables)!!

    the best thing is to run the query on the server (by making a view on SQL SERVER) .. and with each table

    name in the query, add this statement "with (nolock)" .. so you read data as it is (not recently updated = not

    commited transaction), and you don't wait for locks to be free and you don't lock these tables, and any other

    transaction will complete successfully (for you and others) .

    you will write something like this:

    select Invoices.*, Situation.*

    from Situation with (nolock) INNER JOIN Invoices with (nolock)

         ON Situation.SituationID = Invoices.Situation

    where Situation.SituationID =1

    Disadv:

    - when using "with (nolock)", you will not seen comitted transaction (changed one) at this moment ... but you

    can see it at any other time.

    Adv:

    - no hang at all

    - very fast Response

    - Little summary for Locks in SQL Log file.

    also : you may need to add more Memory (RAM) when server still hangs a little after transfering  your queries

    to Server.... becuase every transaction or query is done on server first, then processed data is passed to your

    PC.

    I hope this will help you


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

  • May 27, 2004 at 7:57 am

    #508011

    Hi

    Thanks for your detailed reply...

    For all the SELECT queries I changed with nolock, i can see visible difference on the page loads.

    Aslo found that SQL 2000 SP3 not installed and did installed.

    Let me see the progress if i get any error i'll update.

    Thanks

    -srini

     

  • May 27, 2004 at 10:39 am

    #508062

    There are 2 types of timeouts that you could be facing.  The first is a Connection Timeout, which only occurs when you open the connection to SQL Server.

    The other, and more likely problem in your case, is a Query Timeout.  The default is 30 seconds.  You stated that you most often see timeouts on Inserts, so I'd look at a couple of areas.  Note that you can change the timeout defaults, look up ConnectionTimeout and CommandTimeout or QueryTimeout.

    If you have a system which has a lot of change activity, such as Inserts, Updates and Deletes, then you may be seeing blocking.  This will be futher compounded if you have a large number of indexes on the tables being modified.  Analyze the tables you are updating most to verify that your indexing isn't causing blocking or taking excessive time for index maintenance.

    Before you added the (NOLOCK) hint to your select statements, even they could be preventing your Inserts from completing.  Take a look at Blocking, which can be done fairly simply with SP_WHO2.


    Regards,
    Steve

    Meddle not in the affairs of dragons, for you are crunchy and taste good with ketchup.

Viewing 8 posts - 1 through 7 (of 7 total)

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