How to increase connection timeout? 1

[DeepDiverMom]

We have nightly application-certification packages that run a series of job steps. Several steps into the job, we experience intermittent failures, with the MSSQL error:
Unable to connect: SQL Server is unavailable or does not exist. Unable to connect: SQL Server does not exist or network access denied.

When these errors occur, if we rerun the task once or twice it will start successfully. So these are not situations where the DBMS is down, but instead, the connection failures are likely due to a timeout. To begin isolating the problem, can you please advise how we can increase the appropriate connection-timeout parameter?

Thanks.

Becca

 

[JayKusch]

Open your Enterprise Manager (Start > Programs > Microsoft SQL Server > Enterprise Manager) and navigate to 'Connection Settings' (Tools > Options > Advanced). Increase the 'Login time-out (seconds)' setting to 10, 20, 30 seconds or more, as necessary

Thanks

J. Kusch

 

[DeepDiverMom]

Jay,

I'm pretty new to MS SQL Server, so I am not yet well acquainted with the "currency" of what SQL Server questions are "hard" vs. "easy". I suspect this was probably an "easy" question to answer, but for me (being self-taught), it was not so easy, and your answer saved me an inordinate amount of search time compared with the timeliness of your providing me the answer. Your answer was also very clear and accommodating with keystroke-by-keystroke instructions to avoid any confusion on my part. Very thoughtful and professional response.

Because I value my time as I do, "Thanks" is not enough. The only payment I can make to show my appreciation is the famous "Tek-Tips Purple Star".

I do have a followup question: Now that I have changed that parameter, is that a dynamic change (i.e., takes effect immediately) or is it static requiring a bounce of the SQL Server?

And on that same topic (static vs. dynamic server settings) and now showing my neophyte ignorance, which (if any) SQL Server-parameter settings are static and which are dynamic? (...a reference link listing the answers is sufficient here.)

Thanks again,

Becca

 

[JayKusch]

The tweek you just performed is Dynamic ... does not need a restart of the SQL Server Services.

For a list of Configuration Options that do/and do not need a SQL Server "bounce" are listed on this link.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3wmr.asp

Thanks for the Star ... and welcome to the world of SQL Server - LOL


Thanks

J. Kusch

 

[DeepDiverMom]

I need some follow-on advice and troubleshooting assistance.
I increased the connection timeout setting from 4 seconds to 12 seconds, just preceding our multi-step application run that generated these error results:

Warning DBSB9001:Sybase error: Unable to connect: SQL Server is unavailable or
does not exist. Unable to connect: SQL Server does not exist or network
access denied.
Error DBSB0903: Could not open a DBPROC.
Error DBLG2073: End-user 're_cert2' was unable to sign onto database instance
'BUMBLE:re_cert2' as physical user 're_cert2'.
Error DBCN0558: Failed to signon to the vendor database.

These errors appeared following the same user login successfully completed other job steps, so we know it is not a username/password problem. I increased the timeout specification yet a third time, from 12 seconds to 24 seconds. That also yielded the same error results.

Can someone suggest the next troubleshoot effort I should try?

Thanks,

Becca