February 29, 2008 at 9:06 am
We have an application that connects to a SQL Server 2005 database. The connection is made via ODBC. During the day the users get disconnected from the database with user seeing a mesage someting along the line of "Network connection filure. Database connection lost". The connection loss is random and does not affect any other application that make use of SQL (or not). The user logs on to the applciation which establishes a connection to the database via ODBC and stays connected until they log out
We have checked out most of items, e.g. network issues and replaced switches.
Any ideas if it is SQL Server 2005 doing this?
February 29, 2008 at 8:32 pm
Is the database set to Autoclose?
Exec sp_dboption 'dbname', 'autoclose'
Will tell you.
Exec sp_dboption 'dbname', 'autoclose', 'off'
Will turn it off if it is on.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 3, 2008 at 3:07 am
Hi Jack
Thanks for the feedback. I will check the database on the instance. What is the purpose of this option and does it have a default?
Regards
Pravin
March 3, 2008 at 6:01 am
Autoclose is designed so SQL Server will return resources to the system and the default is off. It should never be on for any application used on a daily basis as opening a closed database is expensive and slow. This likely is not your problem, but I have seen it cause problems.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 4, 2008 at 1:25 pm
have you got a broadcom network card in the server? is it running win server 2003 sp2 ?
if so try turning tcp chimney (via command line) and synattackprotect (in registry) off
i had the same issue over the last six months - been trawling google for answers since the issue started only recently have any come to light. turns out that an option is turned on in win serv 2003 sp2 that the broadcom cards don't properly support and you get this issue. we only implemented this fix recently but i've not had the problem reported since.
(i think there was another setting RSSenable or similar which we also switched off)
hope that helps
March 5, 2008 at 6:17 am
Connection timeout settings?
This can be configured with the SQL Server instance and also with the application connection.
Why would you want an application to keep a connection open for an extended period of time? The app should connect, run its query, then disconnect.
March 5, 2008 at 7:25 am
Hi Alex
Thanks for your input. I have apssed this on to my IT team to check on the server.
Cheers
Pravin
March 5, 2008 at 7:30 am
Hi SRB
I agree that connections should only be made when the application requires data input or extracts. However we are not the original authors of this application and hence cannot verify why it is not done this way.
If the setting was instance based, would this not be a routine and regular timeout based on inactivity? The users could get disconnected whilst they are actually working on the system.
Is there likely to be a bug in SQL Server 2005 where it is infact disconnecting the user due to inactivity but is doing so for all users connected to taht one particluar database.
Please note that the SQL Server instance has several databases. The disconnection as far as I tell only happens for one database.
Finally how can I check to what timeout settings have been configured for the instance?
Cheers
Pravin
March 5, 2008 at 9:01 am
To check the connection settings.
Server Properties==Connections
The other applications may not be noticing because they are connecting/disconnecting and not maintaining a connection. I'm just throwing out a potential theory. If the connection is "dormant" (sql server resetting the connection), then the application may need to reconnect but doesn't because in this case it sounds as though the connection is being made only at application startup.
Have you run a profile trace, or "sp_who2" to see what the connections are looking like? Can you routinely reproduce this problem? If so, then the trace could be quickly run.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply