TIMEOUT error (client thinks it's SQL Server)

  • One of my clients yesterday approached me

    and complained about SQL Server "long running queries".

    They deployed a new version of ASP.NET application

    yesterday and now users randomly having errors on their website.

    When I actually asked them to show me the errors

    (it is captured by their application)

    I only saw general

    "user freeuser

    timeout ...."

    error without any OleDb or anything related to SQL.

    Question

    --------

    If there is SQL Server timeout error will it be captured in SQL Server Log

    or you need to configure SQL to start capturing this type of things?

    How do I prove to the user it is not SQL related timeout?

  • Check whether they have specified any particular amount of time for query timeout.

  • You mean in their .NET code?

    Like CommandProperty.Timeout ?

  • I mean SQL Server property.

    I dont have any idea about .net but if something like timeout is there you can also look into that...

  • riga1966 (4/27/2009)


    If there is SQL Server timeout error will it be captured in SQL Server Log

    or you need to configure SQL to start capturing this type of things?

    How do I prove to the user it is not SQL related timeout?

    SQL Server has no concept of a timeout. A timeout is a client application setting. A client app will have a specific timeout set for queries, default for .net is 30 seconds. If a SQL query hasn't finished in 30 sec, the client app will send SQL a message asking for the query to be aborted and then it will give a timeout error back to the client.

    Two ways to fix timeouts.

    1) Change the timeout setting in the client app. That usually requires code changes.

    2) Optimise the queries so that they run in less time.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi Gila,

    Please correct me if I am wrong

    What is the remote query timeout in SQL Server properties?

  • From Books Online:

    Use the remote query timeout option to specify how long, in seconds, a remote operation can take before Microsoft SQL Server times out. The default is 600, which allows a 10-minute wait. This value applies to an outgoing connection initiated by the Database Engine as a remote query. This value has no effect on queries received by the Database Engine.

    So, in other words, this value is used when SQL Server is acting as a client for a query running somewhere else, usually another database server. It acts the same way as the timeout in a client app, the SQL Server that's initiating the query waits a certain time for the remote machine to finish and, if it hasn't, sends a cancellation and returns a timeout.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gila...You are really g8 🙂

  • You are also great ratheesh.:-D...............pulikuttaaaaaaaaaa:-P

    [font="Comic Sans MS"]+++BLADE+++[/font]:cool:

  • I think I need to see the screenshots with the error.

    Because their Application log

    is very primitive.

    I personally understand that it is most likely the client that

    timeouts. Now the problem is to figure out what SQL causes these timeouts.

    Or maybe it is caused by something else - .NET connection, ADO issues...

  • riga1966 (4/28/2009)


    Now the problem is to figure out what SQL causes these timeouts.

    Try running a trace.

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • if you set up a profiler trace then you should be able to capture this

    just remember to set "RPC/statement/batch starting" and RPC complete events in the trace - if you only capture complete then you won't get the database sql that is timing out

    MVDBA

  • I did.

    I started two traces yesterday.

    But apparently one of them caused disk space issues on C: drive on production server. (Oops!)

    Actually it is interesting.

    When you run a trace for 15 hours and you expect about

    2 million rows there what location is being used for temporary storage

    of that Profiler results.

    I knew about SQL Profiler.

    I am just preparing myself for the situation when

    I do not find any errors in the SQL Profiler results.

    Then I need to prove it is .NET code problem.

    They actually use Microsoft LINQ as a data layer.

    I will read about it now.

    Maybe this causes problems.

    Never heard about it.....

  • See the SQL Profiler EVENTS I chose.

    Is this going to be sufficient to capture TIMEOUTS...

    See attachment...

  • as i said you need RPC starting as well -

    if you know this is going to be a stored proc then you can remove batch starting and t-sql starting and just keep RPC starting and RPC complete

    MVDBA

Viewing 15 posts - 1 through 15 (of 40 total)

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