locked
SQL Timeout from .NET Application RRS feed

  • Question

  • I have a web application that connects to the database to retrieve and display some information. But recently there has been alot of timeout issues happening and it is puzzling me. I'm not a IIS configuration nor am i a SQL 2005 configuration guy so I'm not sure if there are any settings i could have changed but didn't.

     

    I have extended the IIS connection timeout to 600 seconds and the SQL 2005 connection to 600 as well. But still i am facing this issue. Below is the error message. 

    Exception of type 'System.Web.HttpUnhandledException' was thrown.

    Specific Error :

    Error at DB.ExecuteDatatable: Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

    I've taken the piece of SQL code that the application was trying to run when it hit the error and ran it directly in the management studio. It managed to run but took around 1 minute.

    Can anyone assist me on this ?

     

    • Moved by SamAgain Monday, July 12, 2010 10:16 AM better fit (From:Common Language Runtime)
    Monday, July 12, 2010 9:36 AM

All replies

  • Based on error it is not a database connection timeout, but command execution timeout. Your queries might take a lot of time to execute. As a short term solution you could try to increase CommandTimeout property of Command object (not ConnectionTimeout property of connection), but you should profile database and see what is going on on database side that causes it. In many cases it is usually database indexes that cause this issue if they are not optimized. If you are using SQL Server database, for the start, I would recommend to run SQL Profiler tool that comes with SQL Server to see how much time it takes for the queries to execute.


    Val Mazur (MVP) http://www.xporttools.net
    • Proposed as answer by liurong luo Thursday, July 15, 2010 9:47 AM
    Monday, July 12, 2010 10:37 AM
  • I had an old application which had worked fine a couple of years. Then, suddenly it did begin to make a lot of connection timeouts. I added a CommandTimeout (60) to my SqlCommand and everything worked fine. It seems that some Microsoft security update (or something like that) had somehow changed the behavior of the default timeout (0).
    Monday, July 12, 2010 11:59 AM
  • Hello,

    Please take a look at this article: Troubleshooting: timeout expired. Hope it could provide some hints for you. Otherwise, please provide us more information of the issue, for instance, the connection string,  a code snippet, that would be helpful to us to reprodce the issue.

    thanks a lot!


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Thursday, July 15, 2010 10:02 AM
  • Thanks for all the reply.

     

    I would provide the information if it wasn't sensitive. On the profiler, what events do i enable ? 

     

    I tried a few and damn, the files are huge!

    Friday, July 16, 2010 8:42 AM