locked
Timeout RRS feed

  • Question

  • Hi,
    for what reason can I get a "Timeout Expired" error back from an SQL command to a database other than trying to pull too much data from the data base - let me explain.

    Using a VB6 executable, to pull data from an sql2005 database (default instance).

    I am trying to pull circa 288 records(1 days data) from a database which sits on the local machine, this used to work fine pulling 30 days worth of data across a network. I am consistently getting this error, and I have extended the connection.commandtimeout property to as much as 500 seconds.

    So in short is there any other reason for getting the "Timeout Expired" error e.g. database corruption... ??

    Any help would be much appreciated.

    Liam
    Thursday, August 18, 2011 10:36 AM

Answers

  • The database is large but the amount of data which I am pulling from the database is a fraction(1/30) of what I had been pulling from it. I think I may be getting to the root of it, which brings me to the question of what is the correct procedure fro migrating a database from one machine to another?


    If my calculations are correct, you have thousands of rows rather than millions.  I would not expect a timeout, even with a suboptimal plan, unless the query is especially complex.  Make sure your statistics are up-to-date (e.g. sp_updatestats).

    If you want to copy the entire database from one server to another, the easiest method is BACKUP/RESTORE.

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by KJian_ Thursday, August 25, 2011 9:06 AM
    Thursday, August 18, 2011 1:09 PM

All replies

  • Hi,

     

    It is possible that the table is locked by other user. Use NOLOCK hint it dirty read ok for you. For information on Query hint follow http://msdn.microsoft.com/en-us/library/ms181714.aspx

     

    Regards,

     

    Balwant.


    Failure in Life is failure to try... PGDCA-98(South Gujarat University),MCTS (SQL Server 2005)
    Thursday, August 18, 2011 10:45 AM
  • So in short is there any other reason for getting the "Timeout Expired" error e.g. database corruption... ??

    A command timeout occurs when the query executes too long on the database server.  The most common causes for command timeouts are long-term blocking or a poorly performing query.  Check for blocking while the query is executing.  Long blocking may be due a long-running transaction or a proorly written application that waits for user input during an open transaction.  Also, review the execution plan to see if query and index tuning is needed.  But 500 seconds is a very high timeout so I would not suspect query performance as the cause unless your table is very large.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Thursday, August 18, 2011 12:23 PM
  • So in short is there any other reason for getting the "Timeout Expired" error e.g. database corruption... ??

    A command timeout occurs when the query executes too long on the database server.  The most common causes for command timeouts are long-term blocking or a poorly performing query.  Check for blocking while the query is executing.  Long blocking may be due a long-running transaction or a proorly written application that waits for user input during an open transaction.  Also, review the execution plan to see if query and index tuning is needed.  But 500 seconds is a very high timeout so I would not suspect query performance as the cause unless your table is very large.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/


    Hi Dan, thanks for that.

    The database is large but the amount of data which I am pulling from the database is a fraction(1/30) of what I had been pulling from it. I think I may be getting to the root of it, which brings me to the question of what is the correct procedure fro migrating a database from one machine to another?

    Thursday, August 18, 2011 12:51 PM
  • The database is large but the amount of data which I am pulling from the database is a fraction(1/30) of what I had been pulling from it. I think I may be getting to the root of it, which brings me to the question of what is the correct procedure fro migrating a database from one machine to another?


    If my calculations are correct, you have thousands of rows rather than millions.  I would not expect a timeout, even with a suboptimal plan, unless the query is especially complex.  Make sure your statistics are up-to-date (e.g. sp_updatestats).

    If you want to copy the entire database from one server to another, the easiest method is BACKUP/RESTORE.

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by KJian_ Thursday, August 25, 2011 9:06 AM
    Thursday, August 18, 2011 1:09 PM