SQL Server Performance Forum – Threads Archive
Timeout expired
Hello everybody ! I have a timeout expired error from an application written in Delphi and MDAC 2.6 . This is OK on my development machine. But on the customer side i have this particular and horrible message.I already run the profiler and didn’t give advice for indexes.
I already rebuilt the database with script and i still have the same problem. This error occurs randomly with any of the queries.
I already put the command and time out to 0 and nothing better.
Do you think i have to reinstall SQL server 2000 ?
Make sure you disconnect the connection once the transaction finishes which is controlled from application. Try applying latest MDAC (2.7) with SPs from MS. Satya SKJ
Could you post the exact error message. Then we can see if it’s a timeout expired message from the sql server or the application script.
TRue, and also worth checking SQL error logs at both the ends for any informational messages. HTH Satya SKJ
Hi everybody ! I found the solution to my problem. I was working with transactions
and as you know transactions lock some tables thus users didn’t can work with the tables they need within the application and get this obvious timeout expired. Thank you very much to all of you !
You can still work by referring to the LOCKS on SQL Server, have a look around in BOOKS ONLINE> HTH Satya SKJ
I am having the same problems. Here is the error message – [Microsoft][ODBC SQL Server Driver]Timeout Expired. I have the time out set to 180. It occurs randomly. I have checked all my queiries in QA and the response time is less than 10 seconds. I am using MDAC 2.7 and SQL Server 2000 SP3. When the time out error happens it happens in less than 180 seconds. I am stumped at this point. Any help would be appreciated. Here is the connection string I am using which utilizes the IP address of the server which is supposed to increase performance.driver={SQL Server}; server=SERVER1; Address=actual_ip_address,1433; Network=DBMSSOCN; uid=sa<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />wd=;database=DATABASE1<br /><br />Thanks <br /><br />Mac
I’ve found that you can sometimes get a timeout error that returns pretty quickly when you’re unable to establish a connection to the database, could be the network connection getting lost for a few seconds every now and again, it’s happened to me, then again checking your application and using profiler to track the points at which it times out would be the first thing I’d do. Cheers
Shaun World Domination Through Superior Software
MacDaddio:
Where have you set the timeout? In the connectionstring or as a parameter to the connection or command object in ado? Also make sure that you do not store connection objects on ASP session variables or similair. And you could try using an oledb connection instead of an odbc connection as you are using now, though I don’t think it would matter much unless you have problems with your drivers. Seehttp://www.connectionstrings.com/ for examples. /Argyle
Probably not much difference, I agree, but the ADO OLEDB provider is more optimised and offers higher performance than ODBC. Also, when the timeout occurs < 180 seconds, try looking at the ADO Errors Collection. It might give you hints as to why it occurred (ie, any severity < 16 errors).
Everyone, thanks for the feedback. I set the time out when I create the connection as follows:
With cn
.ConnectionString = g_sql_server_connect_str
.CursorLocation = adUseServer
.ConnectionTimeout = 180
.Open
End With This connection stays open while the user is in the application. Could it be that there are too many connections open? The SQL server is set for an unlimited amount. Cn is a global variable which gets used when I make a call to the database for a recordset. The other wierd thing is the users say that when there are more than 3 of them using a certain screen(for the dept) they almost constantly get the ‘Time Out’, could this be more of a network issue rather than an application issue? Thanks Again.
It sounds like you keep your connection open for each application for the entire lifetime of the application, I don’t think this is a wise idea, at the least it’s not at all scalable and not a very good programming practice, you might want to think about modifying your application to only connect to the the database when you actually need to do something with it and then disconnect when you’re finished. Once you’ve established your first connection ADO will pool it and you won’t incur the overhead of recreating the entire connection when you need it again so I don’t see why you need a constant connection in the application. The more users using your application, the more of a burden you’re gonna be putting on the database and you’re bound to start running into problems. You also mentioned that when there are a few users in a specific screen in the application the timeouts occur, if they’re working with the same data this sounds like there could be some locking going on, is the data on these screens brought down in a recordset, held, edited and then the recordset updated? If so you might want to check what locking you’re using on your queries. It’s difficult to give any other suggestions unless you post some code on how you’re querying and modifying the data in these screens, otherwise I might just be spouting off some irrelevant rubbish. You should definatly look into the issue with holding the connection for the duration fo the application and maybe post some code how you’re retrieving (and updating) data in these screens you mentioned. Cheers
Shaun World Domination Through Superior Software
Sorry I didn’t reply sooner but work was piling up. I don’t beleive my connection method would be the problem because there are only 65-75 users of which maybe 20 are hitting the database at the sametime. The statement I get the time out error on is a simple call to the database p_rs.open p_sql,cn, adOpenKeyset, adLockOptimistic(when I need to update or add data) and sometimes on p_rs.open p_sql,cn,as OpenStatic, adLockReadOnly(when I need to display date) the recordsets are then immediatley closed when I am done with them. This is frustrating the dickens out of me and my client. Thanks again four all your input. The data that the users in the one department are using is unique to the product they are working with so I know they are not going after the same records. When using profiler on the SQL Server how can I determine when a transaction or call to the database gets kicked back or timed out or is there someway to determine wether the connection to the database was lost for a few seconds? I am a developer and am not well knowledged how to translate what the profiler is telling me. Thanks again for all the feedback.
I would suggest to refer to this link http://www.sql-server-performance.com/sql_server_profiler_tips.asp] to know about PROFILER. _________
Satya SKJ
]]>