Post reply

Not enough connections.. MS SQL 2008 R2 - Win 2008 R2 server, IIS7 - Classic ASP

  • May 29, 2011 at 12:40 am

    #389613

    it looks like when my page is under heavy load i dont have enough connection..

    and that is killing my site 🙁 i get this from our error system

    Microsoft SQL Server Native Client 10.0

    TCP Provider: An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full.

    We use for conetion this conection strig: SQL and IIS is same server!

    Provider=SQLNCLI10;Server=127.0.0.1,xxxx;Database=xxxxxx;Uid=xxxxxx;Pwd=xxxxxx;

    im wondering if this maybe the wrong connection string to use??

    I have also look at SQL server settings, its set to Unlimited connections..

    Is there something im missing here?

    See settings i have attached a PDF whit screenshots

    The server is BIG has lots of power and lots of ram.

    on heavy load it rungs like 33% cpu and 16 gigs ram. and it has 48 gigs ram total.

    we are at roads end, we have try almost everything in the IIS i can think of, nothing helps.

    So now im asking for help in this forum, as i may suspect its an SQL issue, and not IIS

    any one have any advise??

    Thanks so much... 🙂

    Thanks
    BrownGhost

  • May 31, 2011 at 3:48 am

    #1331856

    I think there are 2 things you should do.

    1) Set a maximum memory value for SQL Server. If you do not do this then buffer pool memory can swamp other memory allocations. On a server that is just running SQL database engine, typically allow 20% of total server memory for the OS and for non-SQL Server tasks. If you have other significant processes also running on your server (IIS, SQL Analysis Services, etc), then make allowance for the memory they use when setting SQL max memory. After the initial setting you can fine-tune the value and maybe get a few more GB assigned to SQL, but it is important to get away from allowing SQL to use up to 2TB of memory wthat your current setting allows.

    2) If doing the above does not solve your problem and no-one else can come up with a better idea, then raise a ticket with Microsoft. You pay for their support, so use it when you have a problem you cannot solve.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • June 2, 2011 at 9:45 am

    #1333201

    Thanks, it did not solve the issue..

    We found out we are under DDOS attacks, and thet is why there are not enough connections..

    looks like cpu works for a bit, then it shots site down for 30 secunds.. then comes back online untill SQL has no more conetions, and the resets again..

    Really annoying that the SQL does not just take more RAM 🙁

    We have 48 GIGA, i have never ever seen more then 14 Giga used on the server total :/

    Thanks
    BrownGhost

Viewing 3 posts - 1 through 3 (of 3 total)

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