Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unexplained Client Disconnects Expand / Collapse
Author
Message
Posted Wednesday, April 14, 2010 3:52 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:04 AM
Points: 219, Visits: 458
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
Post #903643
Posted Wednesday, April 14, 2010 4:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 5,618, Visits: 25,230
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
Post #903655
Posted Thursday, April 15, 2010 10:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:04 AM
Points: 219, Visits: 458
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!
Post #904227
Posted Thursday, April 15, 2010 11:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:27 PM
Points: 12,917, Visits: 32,082
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #904234
Posted Thursday, April 15, 2010 1:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 5,618, Visits: 25,230
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
Post #904332
Posted Thursday, April 15, 2010 1:45 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:04 AM
Points: 219, Visits: 458
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
Post #904359
Posted Monday, April 19, 2010 8:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:04 AM
Points: 219, Visits: 458
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).

Post #906038
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse