CODEWITHSUNDEEP
asp.netsqlsql-server-2008database-connection
Robert
Robert

Reputation: 265

SQL Server Connection Issue

We recently launched a new web site... there are roughly ~150 users active during peak hours. During peak hours, we are experiencing an issue every few minutes, the exception text is listed below.

System.Web.HttpUnhandledException: 
Exception of type 'System.Web.HttpUnhandledException' was thrown. 
---> System.Data.SqlClient.SqlException: The client was unable to establish a connection because of an error during connection initialization process before login. 
Possible causes include the following: 
    the client tried to connect to an unsupported version of SQL Server; 
    the server was too busy to accept new connections; 
    or there was a resource limitation (insufficient memory or maximum allowed connections) on the server. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.)

Our data access layer calls various DataTableAdapters using the following syntax.

EDIT

Yes, da is the name assigned to the DataTableAdapter. There is no connection.Open() because the DataTableAdapter takes care of all that, right?

using(TheDataLayer.some.strongly.typedNameTableAdapters.suchAndSuchTableAdapter da = new TheDataLayer.some.strongly.typedNameTableAdapters.suchAndSuchTableAdapter())
        {
            StronglyTyped.DataTable dt = new StronglyTyped.DataTable();
            da.FillByVariousArguments(dt, ..., ...);
            //da.Dispose();

            return something;
        }

The connection string looks something like:

<add name="MyConnectionString"
        connectionString="Data Source=myDBServerName;Initial Catalog=MyDB;User ID=MyUserName;Password=MyPassword"
        providerName="System.Data.SqlClient" />

I'm trying to rule the problem being in Code. Is there anything "simple" that can be done to minimize this issue?

Thanks.

Upvotes: 1

Views: 4529

Answers (4)

Kashyap Shah
Kashyap Shah

Reputation: 41

We had similar issue which only happenes in our production environment and it was particularly associated with load. During busy time of day we would recieve several of the above mentioned exception.

We gone through a massive investigation around why this exception occurs and did a lot of changes to fix the issue. The defacto change we did which aleviated the problem was connection pool setting by setting min pool size to 1 and max pool size to 10. (It can vary based on your situation)

This issue will be more prevalent when you have several i.e. 1000's of Customer DB and use default connection string (i.e. database=DBName;server=ServerName). We were not explicitly setting min/max pool size hence it took default settings which set Min pool size to 0 and max pool size to 100.

Again, I dont have concrete proof but the theory is that during busy time of the day based on load it made several connection to DB server and DB server was bombarded with a lot of connection request at single point to several databases. Either Application server or DB server did have bandwidth to handle that many connection in a short period of time. Also, it was happening with server with most databases. Though we did not see a lot of connection at a time but Application server was not able to make connection to databases for a short duration when it had surge of requests going in.

After we set min pool size we aliveated this problem as there is atleast one connection to each database which is available all the time and if there is blast of request which required to make connection to several databases we already had atleast one connection to the database available before we request a new one.

Upvotes: 1

komratanomin
komratanomin

Reputation: 85

Maybe unrelated to the actual problem you were facing, but this error is also thrown if you are trying to connect without specifying the correct port along with the database server name.

Upvotes: 0

George Stocker
George Stocker

Reputation: 57907

Without seeing the code that actually opens and uses the connection, it's hard to say where the problem is.

Please update your question with what happens when you create that DataAdapter (I'm guessing that's what da means).

Also, if you're using the using statement, you shouldn't be disposing of the thing you created the using statement for.

Upvotes: 1

Roberto Conte Rosito
Roberto Conte Rosito

Reputation: 2108

Have you tried "Connection Pooling" directly in connection string settings?

Example:

connectionString="....;Pooling=true;Min Pool Size=1;Max Pool Size=10;..."

You can read more info here: http://msdn.microsoft.com/en-us/library/8xx3tyca%28v=vs.71%29.aspx

Upvotes: 4

Related Questions