|
|
You're in the
sql server connect
group:SQL Server does not exist or access denied.
sql server connect:
I get the following error when trying to connect to SQL Server
SQL Server does not exist or access denied. ConnectionOpen (Connect()). Scenario: The application connects to SQL Server very frequently (million+ times per day). It fails with this error message about 100 times. The failure rate is not very high and unfortunately, I can not open one or more connections. I must open connection and close and do this again a million times. So I need to solve this problem. Where would you start? I set timeout to 2 minutes and this helped a lot, but i still get errors.
There are a number of things that could be going on here. Let's get some
more info on your environment What transport are you using (TCP, LPC, NP)? TCP has is less overhead and network reads/writes over NP. Check out the following KB's for tweaking your TCP ack depending on your environment http://support.microsoft.com/kb/328890/ http://support.microsoft.com/default.aspx?scid=kb;en-us;q315669 There is also a limit on the number of open TCP ports that you can have at any given time. There are others under HKLM\SYSTEM\CurrentControlSet\Services\TcpIp check it out on MSDN. Are you using SQL auth? I have seen issues if the DC isn't available (network) and you're using domain users. What is the load of the client(s)/server connecting? Are there any specific loads during failure time? If either are under heavy memory / cpu pressure you could experience dropped connecitons. I would really like to help understand what's going on in your environment. Have you considered using connection pooling? A million connections per day is about 10-12 new connections per second. That could be a significant load depending on how much work is going on in the engine. -- Brad Sarsfield [MSFT] bradsa(at)microsoft.com This posting is provided "AS IS", with no warranties, and confers no rights. [quoted text, click to view] <stevehunter_1@hotmail.com> wrote in message news:1128030310.743829.224010@g47g2000cwa.googlegroups.com... >I get the following error when trying to connect to SQL Server > > SQL Server does not exist or access denied. > ConnectionOpen (Connect()). > > Scenario: > > The application connects to SQL Server very frequently (million+ times > per day). It fails with this error message about 100 times. > > The failure rate is not very high and unfortunately, I can not open one > or more connections. I must open connection and close and do this > again a million times. > > So I need to solve this problem. Where would you start? I set > timeout to 2 minutes and this helped a lot, but i still get errors. >
Brad, Thanks for your help. see my replies below:
[quoted text, click to view] Brad Sarsfield wrote: > There are a number of things that could be going on here. Let's get some > more info on your environment > > What transport are you using (TCP, LPC, NP)? > TCP has is less overhead and network reads/writes over NP. > Check out the following KB's for tweaking your TCP ack depending on your > environment > http://support.microsoft.com/kb/328890/ > http://support.microsoft.com/default.aspx?scid=kb;en-us;q315669 > I am using TCP/IP. Do I apply these KB articles to the Client or the SQL Server? The SQL Server is W2000, Client is 2003 [quoted text, click to view] > There is also a limit on the number of open TCP ports that you can have > at any given time. > There are others under HKLM\SYSTEM\CurrentControlSet\Services\TcpIp > check it out on MSDN. > At peak, we will open/close 100 connections a second. [quoted text, click to view] > Are you using SQL auth? > I have seen issues if the DC isn't available (network) and you're using > domain users. > Using SQL Auth. [quoted text, click to view] > What is the load of the client(s)/server connecting? Are there any specific > loads during failure time? > If either are under heavy memory / cpu pressure you could experience > dropped connecitons. During heavy use, we see the problem more, but I do not attribute it to load, i just attribute it to more connection attempts. So more connection attempts=more failures. [quoted text, click to view] > I would really like to help understand what's going on in your environment. > > Have you considered using connection pooling? A million connections per day > is about 10-12 new connections per second. That could be a significant load > depending on how much work is going on in the engine. > I have considered connection pooling, but there is no performance issue doing it this way. It works very well otherwise. I would prefer a simple solution to the problem and would tackle connection pooling or some other method in the future when it is time to upgrade the application. [quoted text, click to view] > --
> Brad Sarsfield [MSFT] bradsa(at)microsoft.com > > This posting is provided "AS IS", with no warranties, and confers no rights. > > <stevehunter_1@hotmail.com> wrote in message > news:1128030310.743829.224010@g47g2000cwa.googlegroups.com... > >I get the following error when trying to connect to SQL Server > > > > SQL Server does not exist or access denied. > > ConnectionOpen (Connect()). > > > > Scenario: > > > > The application connects to SQL Server very frequently (million+ times > > per day). It fails with this error message about 100 times. > > > > The failure rate is not very high and unfortunately, I can not open one > > or more connections. I must open connection and close and do this > > again a million times. > > > > So I need to solve this problem. Where would you start? I set > > timeout to 2 minutes and this helped a lot, but i still get errors. > >
I'd try applying it first on server. It sounds like you may be getting into
the situation where you could be running out of available socket connections for new connections or memory for new connections. For the first two you can try to play with the following two reg keys. (Decrease the TcpTimedWaitDelay). For the second you may need to add more memory (if sql is infact running out of memory). From: http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/networking/tcpip03.mspx#EGAA TcpTimedWaitDelay Key: Tcpip\Parameters Value Type: REG_DWORD-time in seconds ValidRange: 30-300 (decimal) Default: 0xF0 (240 decimal) Description: This parameter determines the length of time that a connection stays in the TIME_WAIT state when being closed. While a connection is in the TIME_WAIT state, the socket pair cannot be reused. This is also known as the 2MSL state because the value should be twice the maximum segment lifetime on the network. See RFC 793 for further details. -- Brad Sarsfield [MSFT] bradsa(at)microsoft.com This posting is provided "AS IS", with no warranties, and confers no rights. [quoted text, click to view] <stevehunter_1@hotmail.com> wrote in message news:1128110959.712139.326900@g43g2000cwa.googlegroups.com... > Brad, Thanks for your help. see my replies below: > > > Brad Sarsfield wrote: >> There are a number of things that could be going on here. Let's get some >> more info on your environment >> >> What transport are you using (TCP, LPC, NP)? >> TCP has is less overhead and network reads/writes over NP. >> Check out the following KB's for tweaking your TCP ack depending on >> your >> environment >> http://support.microsoft.com/kb/328890/ >> http://support.microsoft.com/default.aspx?scid=kb;en-us;q315669 >> > > > I am using TCP/IP. Do I apply these KB articles to the Client or the > SQL Server? The SQL Server is W2000, Client is 2003 > > >> There is also a limit on the number of open TCP ports that you can >> have >> at any given time. >> There are others under HKLM\SYSTEM\CurrentControlSet\Services\TcpIp >> check it out on MSDN. >> > > At peak, we will open/close 100 connections a second. > >> Are you using SQL auth? >> I have seen issues if the DC isn't available (network) and you're >> using >> domain users. >> > > Using SQL Auth. > >> What is the load of the client(s)/server connecting? Are there any >> specific >> loads during failure time? >> If either are under heavy memory / cpu pressure you could experience >> dropped connecitons. > > During heavy use, we see the problem more, but I do not attribute it to > load, i just attribute it to more connection attempts. So more > connection attempts=more failures. > > >> I would really like to help understand what's going on in your >> environment. >> >> Have you considered using connection pooling? A million connections per >> day >> is about 10-12 new connections per second. That could be a significant >> load >> depending on how much work is going on in the engine. >> > > I have considered connection pooling, but there is no performance issue > doing it this way. It works very well otherwise. I would prefer a > simple solution to the problem and would tackle connection pooling or > some other method in the future when it is time to upgrade the > application. > > >> -- >> Brad Sarsfield [MSFT] bradsa(at)microsoft.com >> >> This posting is provided "AS IS", with no warranties, and confers no >> rights. >> >> <stevehunter_1@hotmail.com> wrote in message >> news:1128030310.743829.224010@g47g2000cwa.googlegroups.com... >> >I get the following error when trying to connect to SQL Server >> > >> > SQL Server does not exist or access denied. >> > ConnectionOpen (Connect()). >> > >> > Scenario: >> > >> > The application connects to SQL Server very frequently (million+ times >> > per day). It fails with this error message about 100 times. >> > >> > The failure rate is not very high and unfortunately, I can not open one >> > or more connections. I must open connection and close and do this >> > again a million times. >> > >> > So I need to solve this problem. Where would you start? I set >> > timeout to 2 minutes and this helped a lot, but i still get errors. >> > >
Brad
I tried this with no success. Also, I checked my ODBC Admin screen and note that the "SQL Server" driver has enabled connection pooling. So I assume in my MFC application (using CDatabase with ODBC) it is automatically connection pooling for me? There are no network issues connecting to the server (i am able to "ping" millions of times all day long with success). Any ideas? [quoted text, click to view] Brad Sarsfield wrote:
> I'd try applying it first on server. It sounds like you may be getting into > the situation where you could be running out of available socket connections > for new connections or memory for new connections. For the first two you > can try to play with the following two reg keys. (Decrease the > TcpTimedWaitDelay). For the second you may need to add more memory (if sql > is infact running out of memory). > > From: > http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/networking/tcpip03.mspx#EGAA > > TcpTimedWaitDelay > Key: Tcpip\Parameters > > Value Type: REG_DWORD-time in seconds > > ValidRange: 30-300 (decimal) > > Default: 0xF0 (240 decimal) > > Description: This parameter determines the length of time that a connection > stays in the TIME_WAIT state when being closed. While a connection is in the > TIME_WAIT state, the socket pair cannot be reused. This is also known as the > 2MSL state because the value should be twice the maximum segment lifetime on > the network. See RFC 793 for further details. >
Steve,
Lets make sure that this is the problem that you're running into: If on your server you run netstat -aon and you have thousands of ports in the TIME_WAIT state your clients will run into this problem. A while back we wrote a KB article on this problem. We should also try to adjust your MaxUserPort in addition to the TcpTimedWaitDelay setting on the server. Check this out and let me know how this goes. http://support.microsoft.com/kb/328476 With regards to connection polling the ODBC admin connection pooling tab 'enable' 'disable' refers to performance monitoring of the pooled connections. To pool the conneciton your appliction must follow the five steps outlined here. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcodbc_connection_pooling.asp here's an examle as well http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp Let me know if the steps in the KB work for you. -- Brad Sarsfield [MSFT] bradsa(at)microsoft.com This posting is provided "AS IS", with no warranties, and confers no rights. [quoted text, click to view] <stevehunter_1@hotmail.com> wrote in message news:1129087454.925724.10410@f14g2000cwb.googlegroups.com... > Brad > > I tried this with no success. > > Also, I checked my ODBC Admin screen and note that the "SQL Server" > driver has enabled connection pooling. So I assume in my MFC > application (using CDatabase with ODBC) it is automatically connection > pooling for me? > > There are no network issues connecting to the server (i am able to > "ping" millions of times all day long with success). > > Any ideas? > > > Brad Sarsfield wrote: >> I'd try applying it first on server. It sounds like you may be getting >> into >> the situation where you could be running out of available socket >> connections >> for new connections or memory for new connections. For the first two you >> can try to play with the following two reg keys. (Decrease the >> TcpTimedWaitDelay). For the second you may need to add more memory (if >> sql >> is infact running out of memory). >> >> From: >> http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/networking/tcpip03.mspx#EGAA >> >> TcpTimedWaitDelay >> Key: Tcpip\Parameters >> >> Value Type: REG_DWORD-time in seconds >> >> ValidRange: 30-300 (decimal) >> >> Default: 0xF0 (240 decimal) >> >> Description: This parameter determines the length of time that a >> connection >> stays in the TIME_WAIT state when being closed. While a connection is in >> the >> TIME_WAIT state, the socket pair cannot be reused. This is also known as >> the >> 2MSL state because the value should be twice the maximum segment lifetime >> on >> the network. See RFC 793 for further details. >> >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |