SQL Server Performance Forum – Threads Archive

Help – timeout

Hi I have had real problems with our SQL server and need some help/ advice on pinpointing the problem: I have a number of VB applications that use SQL server 2000 and they suddenly stopped working with either a "recordset closed" error (so obviously SQL server was not opening the recordset even though the EOF test was in the VB application) or a "timeout error". These programs mainly use SERVER01 but there is a linked server to SERVER00 which is used occasionally just to retrieve some small amount of data. In the last week i have been having problems with this link being locked for roughly 10 minutes which caused all the VB programs to have a "connection busy" error. To try and resolve this i changed the connection timeout property in the linked server to 180 seconds from 0 seconds. However about 15 minutes after i set this property the problems detailed above with the "timeout error" etc. started happening. These lasted about 2 hours and then finally we rebooted the server and everything seemed to work fine. I know that microsoft say with the linked server there are sometimes locks due to a "stressed linked server" and that the bug fix will be released in SP4 but i don’t know if this problem is connected or something completely random. Has anyone had similar problems or can suggest anything to look out for or test?
Thanks
CE

I referred thishttp://vyaskn.tripod.com/sql_odbc_timeout_expired.htm link before when we had such trouble, and thishttp://www.sql-server-performance.com/linked_server.asp in this site suggests In SQL Server 2000, when creating a linked server, in the Server Options tab, there is an option called Connection Timeout and Query Timeout. The default settings for both of these options is 0, which means that there is no timeout value for either of these options. This means that long running remote queries will not time out. If you suspect that some of the remote queries that will be run may take "too long" and unnecessarily use up too many server and network resources, you can enter a time in seconds that you are willing to wait for a remote query to run, but if the query takes longer than the amount you have specified, then it will be aborted. You can use this feature to ensure that no long running queries take up more resources than they should.
… about linked server. How about controlling the disconnection from application when the transaction is finished? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>