SQL Server Performance

Timeout Expired!

Discussion in 'T-SQL Performance Tuning for Developers' started by djid, Nov 20, 2002.

  1. djid New Member

    Hi, All:
    I met a problem during using SQL Server. There is a real-time application which will accept data from a kind of monitor device
    and write them into database. The device samples a serial of measuring values(such as electric current, voltage) per minute which are called "history sampling data" and sends them to the application.When the application receives the data, it will write them into database.So every minute the application will execute a writing operation to database. The running envionment of the application is described as following:
    WinNT4.0Server(sp6),
    SQLServer7.0,
    VC++6.0,
    ODBC API.
    Now the problem is after a period of regular running(about 1 month), I find the application cant write sampling data into database any more. And trace&debug tool shows one message "Timeout Expired". I exit my application but it does not work. I must restart OS it will restore writing data to database.Because it is a kind of
    real-time monitoring application users cant permit OS to restart after a period of running. So what can I do?
    thanks a lot!
  2. trifunk New Member

    Are you establishing a connection to the database every minute or holding a connection in memory and using that when you insert? I would think it better to re-establish the connection every time and make sure it's closed properly afterwards.

    If you are creating a new connection each time it might be best to check that the connections are indeed being released in all cases, I'm not 100% on this but the timeout could be a connection timeout because too many connections are trying to be made to the database (some connections havn't been closed succesfully). Also is the application and database on the same box? Could be a network problem stalling the connection?

    Just a hunch though.

    Cheers
    Shaun

    World Domination Through Superior Software
  3. djid New Member

    I established a connection to the database as soon as the application started and the connection was held in memory.<br />And I think timeout I have met is not connection timeout but query timeout because when I set the value of query timeout by using the function CDatabase:<img src='/community/emoticons/emotion-7.gif' alt=':S' />etQueryTimeout() the period of regular running will be different.Greater the value is set the period is longer.
  4. bradmcgehee New Member

    By default, queries don't ever time out. The only way queries time out is if you have set a timeout somewhere.

    Assuming it is a query time out, because a time out period has been set somewhere in code, the best approach is to identify the query causing the problem and analyze it. There are lots of reasons why a query might take along time, more than I can list here. I would first recommend you use Profiler to capture a trace and identify the query with the problem, then go from there, looking at its execution plan, checking indexes, etc. I would also look for potential blocking to see if that might be a possible cause.

    Another potential problem could be a memory leak caused by a MDAC component (this is known issue in some versions of MDAC. You might also want to consider upgrading all machines running your code to the latest version.

    This is a lot to check, but you must try each thing, one at a time, until you narrow the problem down. This is something I do almost every day.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page