SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unexplained Client Disconnects


Unexplained Client Disconnects

Author
Message
Hawkeye_DBA
Hawkeye_DBA
SSC Eights!
SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)

Group: General Forum Members
Points: 868 Visits: 566
I have a SQL Express vs. 2005 9.0.4035 running on virtual server, Windows 2003 Standard SP2 with 1 GB RAM and 1 dual-core xeon processor.

The problem:

I moved a SQL 2000 database to the above server and kept it in compatibility mode (80). Users can connect just fine, and most of the time they do not have issues.

However, if they leave their computer idle for more than 30 minutes, and sometimes for no reason at all they get an ODBC Network Error: Connection to server failed.

Some users are connecting with Named Pipes and others with TCP/IP. I believe those connecting with TCP/IP are NOT experiencing this.

This did NOT happen when the database was on SQL Server 200 Standard Ed. Any ideas or bugs or anything known about this?

Thanks in advance!

Hawkeye DBA
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15881 Visits: 25280
Hawkeye_DBA
Users can connect just fine, and most of the time they do not have issues.


How, through what program are the users connecting? SSMS / Web application or?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Hawkeye_DBA
Hawkeye_DBA
SSC Eights!
SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)

Group: General Forum Members
Points: 868 Visits: 566
They are connecting with an application installed on their desktop client, which uses and ODBC connection.

Since I'm not the system analyst I do not know much about the program except that it was built to run on SQL 2000 so we are running it in compatibility mode.

I've considered installing SNAC but I read a Microsoft article that advised against it for apps built on 2000 or earlier that are running in a 2005 environment, apparently the old driver is more compatible.

I don't know what I'm over looking - I guess that's what I'm asking from the community... any ideas?

Thanks for your input!
Lowell
Lowell
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72546 Visits: 40945
just hazarding a guess, but since this is running on Express, could the AUTOCLOSE setting of the database be causing the disconnect of the client connections, when SQL Server decides it has been idle for a while?

on express versions, AUTOCLOSE is true by default, where on server verison it is not...you can always change it to false.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15881 Visits: 25280
Hawkeye_DBA

SQL Express has a unique database setting, which I believe is new to sQL 2005 ... here is a link to check if this is your problem.

http://technet.microsoft.com/en-us/library/bb264564(SQL.90).aspx

From the above:
The sqlservr.exe process that is started is kept running for a while after the last connection to the instance is closed. Therefore, it doesn't need to be restarted if another connection is opened. The length of time it stays around is set by the sp_configure option "user instance timeout". By default, this is set to 60 minutes but you can use the sp_configure command to change this.


From SQL 2005 Books On Line:
The User Instance Timeout option that you can access through sp_configure is not supported in Microsoft SQL Server 2005. This option works only with SQL Server 2005 Express Edition (SQL Server Express).


Further:
sp_configure ‘user instance timeout’

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'user instance timeout', 5;
GO

Where 5 is the minimum value and 65535 is the maximum value.

You need sp_configure ‘show advanced options’ to view and set the timeout. For more information about show advanced options, see Setting Server Configuration Options in SQL Server 2005 Books Online.

You can set the user instance timeout in both parent instance and user instance.

When a user instance starts, it always gets the time out value from the parent instance. However, once the user instance starts, it can use the sp_configure to change the timeout value that is valid only for this specific instance.


Check out that value and see if it could be the cause of your problem.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Hawkeye_DBA
Hawkeye_DBA
SSC Eights!
SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)

Group: General Forum Members
Points: 868 Visits: 566
Hi Ron,

Thanks for the reply!

I looked at this setting, however I have user instances turned Off so it is disregarded :-(

I thought it may have to do with the tcp/ip keep alive feature that is new to 2005 and that perhaps when the database server is "asking" for a reply from the client that maybe that client is not responding due to how it was programmed and how that program is connected. What do you think about that as a possibility? I'd hate for it to be that since that requires a registry change on the client side.

The other worthy mention here is that I have another database on that server and it is connected to by a .net application which does not time out... odd.

Thanks again for your ideas!! I'll keep looking :-)
Hawkeye_DBA
Hawkeye_DBA
SSC Eights!
SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)

Group: General Forum Members
Points: 868 Visits: 566
Hi, thanks for the idea, unfortunately Auto close is off on this database.

The problem appears to be related to named pipe connections vs tcp/ip. I will post the results of additional testing since we switched this user from named pipes to tcp/ip (indicating the port number on the odbc connection).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search