• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

how to increase sql 2005 server database number of connections?

  We have a web application which needs big number of database connections. whenever we are trying to publish sites it is failing and when we invistigated it we found that SQL server policy is accepting 300 database connections as our vendor said & we need to increase the number of database connections.
  I have opened the Managment Studio, right click on the server, properties the I moved to connections tab and I found the maximum number of concurrent connections is "0" which supposed to mean unlimited number of connections
  I contiued my invistigation and I found that due to a security feature of Windows Server 2003, Windows may drop connections if the requests arrive faster than SQL Server can service them in order to avoid server SYN flood. This security feature implements a finite queue for incoming TCP connection requests which results in the following error:

ProviderNum: 7, Error: 10054, ErrorMessage: "TCP Provider: An existing connection was forcibly closed by the remote host ...

What I need to know how I can increase the number of database connections and what are the side effects (if there any?)

Please help me it is an urgent issue
  • 2
1 Solution
Raja Jegan RSQL Server DBA & ArchitectCommented:
SQL server allows a maximum of  32,767 connections..
And change it to either 600 or 1000 and check it out..

Another workaround would be to fine tune your existing queries, so that they execute faster releasing the connections to waiting users..
DWTCITAuthor Commented:
That means if I keep it 0 will it give me 32,767 connections? or it is better to set it as "32,767"
Raja Jegan RSQL Server DBA & ArchitectCommented:
Keeping it to 0 allows a maximum of 32767 by default..
If you are sure that you need more no. of connections say, 600 or 800 then specify it over here for better performance.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now