Unexplained Client Disconnects

  • 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

  • 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[/url]
    Before posting a performance problem please read[/url]

  • 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!

  • 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!

  • 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[/url]
    Before posting a performance problem please read[/url]

  • 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 🙂

  • 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).

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply