Timeout properties question

[JtheRipper]

Hi there,

I would like some clarification on the timeout properties that can be set in SQL Server.

Some background first:
Some applications are experiencing Timeout errors on some of out SQL Servers. The error is:
Connection failed
SQLState: 'S1T00'
SQL Server Error: 0
[Microsoft][ODBC SQL Server driver]Timeout experienced

As far as I know you have 3 places where you can set these timeout properties:
1. sp_configure
2. In EM, right click a server, properties and connections
3. Open EM, Tools, Options, Advanced.

I would like to know which one of the 3, if any, (if there are more I just do not know of them ) I have to go and play with in order to try and solve my problem. From what I can see the sp_configure does not have anything to do with option 3 mentioned above because my remote login timeout is set to 300, but when I use option3 I see Login-timeout is set to 4.
Does Option3 only cater for logins locally and sp_configure for remote logins?
Can I change the settings in Option3 from a remote pc with EM installed?

Any info will be greatly appreciated.
Thanks,
J.

 

[mrdenny]

J,
I beleive that option 3 reverres to the settings for the Enterprise Manager client it self, and not the server. Options 1 and 2 are the ones to reconfigure the server.

 

[JtheRipper]

Thanks for the confirmation!

 

[SQLSister]

This is the worst way to solve your problem. YOu need to find out what queries/stored procedures that the application is timing out on and then fix them so they run efficiently. For instance you need to replace any cursors or add correct indexes or get rid of other inefficiencies such as using the not in command instead of a join.

Questions about posting. See faq183-874

 

[JtheRipper]

Hi,

I agree with you SQL Sister, but the problem is that we do not even know if the timeout is a query- or login timeout, and it only happens sometimes with different apps almost each time. We have huge network congestion in the area where we are getting this problem, so that might also contribute to the problem. We are thinking of changing the application and odbc connection to SQL authentication instead of NT authentication to eliminate the traffic between the app and domain controller.

Any ideas/suggestions are most welcome.

Thanks,
J.