none
sqlException Timeout expired RRS feed

  • Question

  • Hi all,

    I have a problem with a newly developed VB .Net application and I'm hoping someone can help me.

    In my application I am using a data set / data adapter to perform a database update via a stored procedure. Most of the time the update happens in 2 seconds, or less. Intermittently my users receive a timeout error when trying to do the update as follows:

    System.Data.Sqlclient.SqlException: Timeout expired. The timeout period elasped prior to completion of the operation of the server is not responding.

    Everything I can find on the net says to increase the timeout value, which imo is not a very good solution. I've increased the timeout value from 30 seconds to 60 and then 90 seconds but I'm still seeing the error.

    What I want to know is what could be causing this type of error on the server side? What can I do to determine what is causing the delay/backup? I don't anything indicitive in the server logs or in the sql logs that would explain this type of behavior.

    I really need to get this issue worked out but I'm stumped as to how to proceed so any information would be fantastic.

    Thank you!


    Thursday, September 27, 2007 8:28 PM

All replies

  •  Jake Fosheezee wrote:
    Hi all,

    I have a problem with a newly developed VB .Net application and I'm hoping someone can help me.

    In my application I am using a data set / data adapter to perform a database update via a stored procedure. Most of the time the update happens in 2 seconds, or less. Intermittently my users receive a timeout error when trying to do the update as follows:

    System.Data.Sqlclient.SqlException: Timeout expired. The timeout period elasped prior to completion of the operation of the server is not responding.

    Everything I can find on the net says to increase the timeout value, which imo is not a very good solution. I've increased the timeout value from 30 seconds to 60 and then 90 seconds but I'm still seeing the error.

    What I want to know is what could be causing this type of error on the server side? What can I do to determine what is causing the delay/backup? I don't anything indicitive in the server logs or in the sql logs that would explain this type of behavior.

    I really need to get this issue worked out but I'm stumped as to how to proceed so any information would be fantastic.

    Thank you!


    Perhaps SQL Server is still holding a lock on one of your tables ?  You can check this by executing the sp_locks stored procedure.

    Perhaps there are pending (uncommitted / unrollbacked) transactions ?

    Thursday, September 27, 2007 9:47 PM
  • How often do you get into this situation? There are several potential issues:

    1. Application does not close all the connections properly and eventually ends up with the set of opened connections and not able to open new one anymore. You could try to use SQL Server Management Studio or Query Analyzer to execute sp_who stored procedure to see how many connections opened by each specific user. Ideally it should be no more than one opened connection per user at a time.

     

    2. Some process (could be part of your application) opened transaction and your application cannot make updates until transaction will be committed. The only way to avoid it is to keep transaction as short as possible to release all the locks against the tables

     

     

    Friday, September 28, 2007 10:29 AM
    Moderator
  • Thank you for the suggestions guys. I will run those stored procedures and check out the results.

    As far as opening and closing connections, I thought when I used a data set/ data adapter it was taken care of without any additional code. I just run the update method for the adapter which is linked to a stored procedure on the sql server. The stored procedure has the inserts and updates wrapped in transactions.

    Don't know if I'm doing something wrong but if experience is any indicator, I am.

    Thanks much!
    Friday, September 28, 2007 7:13 PM
  •  Jake Fosheezee wrote:


    As far as opening and closing connections, I thought when I used a data set/ data adapter it was taken care of without any additional code.
    Not quite.
    The DataAdapter will only close the connection, when the data-adapter had to open the connection itself.
    For instance, if you just call myDataAdapter.Fill on a closed connection, the data-adapter will close the connection.
    If you first open the connection yourself, and call DataDadapter.Fill afterwards, the data-adapter will not close the connection.


    I just run the update method for the adapter which is linked to a stored procedure on the sql server. The stored procedure has the inserts and updates wrapped in transactions.[/]
    I'm not sure if the connection-stuff applies here as well, but I think so.
    I would certainly check if the connection is still open, and close it if you don't need it anymore for a while.
    (And, I would also make use of a try/finally block so that the connection is certainly closed, even if an exception occurs).


    Sunday, September 30, 2007 6:43 PM