x

how to troubleshoot sql 2008 dropping connections

We successfully ran apps against databases on sql 2000 server. Now that the databases have been moved to sql 2008 r2, clients are intermittently dropped throughout the day. No hints in the error logs of Windows or SQL. Logged pings of server every minute shows max time of < 30 ms. Server runs under VMWare but is the only server on the host. Performance monitor shows system barely using available resources. What else could be causing this?
more ▼

asked Feb 14, 2011 at 10:16 AM in Default

smeachum gravatar image

smeachum
32 1 1 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Can you explain the circumstance that you mention as "clients are intermittently dropped", do you mean they lose a network connection (ie they cant ping the SQL server) or that they lose database connectivity (ie a request for data from the client results in an error)?

A ping to the SQL Server will only confirm the network between the client and the server is good, sometimes only between the client and the server NIC rather than even the server OS. This wont confirm a link to the SQL server database engine, or to any specific database.

Are the clients successfully getting the data at times? Have any changes been made (or indeed not made, ie left at default on the new server) regarding connection pooling? Does the database have Auto_close turned off?
more ▼

answered Feb 14, 2011 at 11:10 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 75 78 108

Clients do not lose network connectivity or even connectivity to the server there the app's .exe file is located. Only connection to the database is lost and it happens with various databases on the SQL server running different apps. Most often this happens during a period of inactivity on the client side, but some users have reported loss during data entry.

The ping time test was run because network interruption was suspected as a cause.

Clients have no problem connecting to the databases. Once the app is closed, the client re-starts the app and continues to work. The server was installed with default settings. Not susre where to set/change connection pooling. Max concurrent connections to the server is 0 (unlimited). Auto_close for the databases experiencing the most disconnects are all set to False.
Feb 14, 2011 at 11:23 AM smeachum
so what error do you get?
Feb 14, 2011 at 11:27 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x46
x9

asked: Feb 14, 2011 at 10:16 AM

Seen: 2157 times

Last Updated: Feb 14, 2011 at 10:16 AM