none
System.Data.SqlClient.SqlException (0x80131904): Timeout expired.

    Question

  • Hi,

    I have an application that has started failing with the above timeout error. 

    Can anyone advise on the best way to identify exactly which stored procedure is failing? 

    Can I use the Profiler for this purpose?

    I simply want to know where the delay is occuring so that we can optiimise SQL statement.

    I can recreate the problem so no issues there.

    Regards,


    Cossy


    Monday, January 28, 2013 10:00 AM

All replies

    1. How to Identify Slow Running Queries with SQL Profiler
    2. From Attention Event Class, "Cancel operations can also be seen as part of implementing data access driver time-outs".
    3. How is your SQL Server errorlog & Windows Logs looking?
    4. Do you see any deadlocks?
    5.  You can look at CommandTimeout property on the SqlCommand object which makes database call. The default is 30 (seconds). Setting it to 0 will allow the command to run indefinitely until the command completes.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful, if a post was useful to help other user's find a solution quicker.


    • Edited by anuragsh Monday, January 28, 2013 10:29 AM
    Monday, January 28, 2013 10:26 AM
  • >>>Can I use the Profiler for this purpose?

    Yes, but cannot end users  help  you when they use the application? What operation do they do while getting timeouts, so you can identify what query was it running. That is an application throws the error not SQL Server. 

    Turn on SQL Server Profiler  and try "Execution Warning" (reports warning that occurred during the execution of SQL statement or procedure) or "User Error Message" (the error message displayed to the user in the case of an error or exception).


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, January 28, 2013 10:30 AM
  • Hi Uri,

    Thanks for the response.

    This isn't an interactive application. Its a background activity that runs a number of analysis models.

    I want some evidence of what is causing the issue so that I can identify exactly where the issue is occuring. Once I have localised the issue, I should be able to optimise it.

     

     


    Cossy

    Monday, January 28, 2013 10:44 AM
  • Cossy, let us know your thoughts on steps mentioned above.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful, if a post was useful to help other user's find a solution quicker.

    Monday, January 28, 2013 10:50 AM
  • Hi Anurag,

    Thanks for the reply.

    There are no error messages in ether the SQL or Windows logs

    There are no errors relating to dealocks either.

    I dont want to change the timeout value on the SQL Command as that will mask the issue rather than resolve it. 

    Ill look at suggestions 1 & 2 now and hopefully that will point me in the right direction. 

    Cossy   


    Cossy

    Monday, January 28, 2013 10:52 AM
  • Yup, 1 & 2 will help you to narrow it to queries that would need a fix.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful, if a post was useful to help other user's find a solution quicker.

    • Marked as answer by Cozzie Cosmas Monday, January 28, 2013 11:07 AM
    • Unmarked as answer by Cozzie Cosmas Monday, January 28, 2013 11:07 AM
    Monday, January 28, 2013 10:55 AM