Connection count gone wild after upgrade to SQL2k5!

We upgraded our server last week to SQL 2005. Prior to this on SQL2k we
averaged 100 connections to the server from our web servers and in-house
applications. All we did was update our application config files so the
connection strings pointed at the new server and now we are up to over
5000!!!!! connections. All of our connection strings are either at the
default max connections or lowered to 50-75. We only have 2 web servers
hitting this SQL box and probably a couple dozen workstations running
windows apps in a very subdued manner, i.e. not db intensive at all.
How could this happen? I'm tempted to alter the server config and force a
max# connections but I'm worried what will happen if I do.
Any input/ideas?How do you count the number of connections?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23VDKUMIoGHA.4800@.TK2MSFTNGP04.phx.gbl...
> We upgraded our server last week to SQL 2005. Prior to this on SQL2k we
> averaged 100 connections to the server from our web servers and in-house
> applications. All we did was update our application config files so the
> connection strings pointed at the new server and now we are up to over
> 5000!!!!! connections. All of our connection strings are either at the
> default max connections or lowered to 50-75. We only have 2 web servers
> hitting this SQL box and probably a couple dozen workstations running
> windows apps in a very subdued manner, i.e. not db intensive at all.
> How could this happen? I'm tempted to alter the server config and force a
> max# connections but I'm worried what will happen if I do.
> Any input/ideas?
>|||Perfmon - SQLServer:General Statistics -> User Connections
"Remus Rusanu [MSFT]" <remus.rusanu@.online.microsoft.com> wrote in message
news:uBVNBWIoGHA.3532@.TK2MSFTNGP04.phx.gbl...
> How do you count the number of connections?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23VDKUMIoGHA.4800@.TK2MSFTNGP04.phx.gbl...
>> We upgraded our server last week to SQL 2005. Prior to this on SQL2k we
>> averaged 100 connections to the server from our web servers and in-house
>> applications. All we did was update our application config files so the
>> connection strings pointed at the new server and now we are up to over
>> 5000!!!!! connections. All of our connection strings are either at the
>> default max connections or lowered to 50-75. We only have 2 web servers
>> hitting this SQL box and probably a couple dozen workstations running
>> windows apps in a very subdued manner, i.e. not db intensive at all.
>> How could this happen? I'm tempted to alter the server config and force
>> a max# connections but I'm worried what will happen if I do.
>> Any input/ideas?
>|||Are you using Service Broker activation by any chance? This would include
also features like SqlDependency or Web pages SqlCacheDependency that are
using activation behind the scenes. Activation can cause the perf counter to
go to abnormaly high values, but is only a perfcounter issue (is not
decremented properly).
You can confirm if the connections really grow or is just the a counter
problem by using a management view, like sys.dm_exec_connections.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O8JnogIoGHA.4728@.TK2MSFTNGP03.phx.gbl...
> Perfmon - SQLServer:General Statistics -> User Connections
> "Remus Rusanu [MSFT]" <remus.rusanu@.online.microsoft.com> wrote in message
> news:uBVNBWIoGHA.3532@.TK2MSFTNGP04.phx.gbl...
>> How do you count the number of connections?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> HTH,
>> ~ Remus Rusanu
>> SQL Service Broker
>> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
>> news:%23VDKUMIoGHA.4800@.TK2MSFTNGP04.phx.gbl...
>> We upgraded our server last week to SQL 2005. Prior to this on SQL2k we
>> averaged 100 connections to the server from our web servers and in-house
>> applications. All we did was update our application config files so the
>> connection strings pointed at the new server and now we are up to over
>> 5000!!!!! connections. All of our connection strings are either at the
>> default max connections or lowered to 50-75. We only have 2 web servers
>> hitting this SQL box and probably a couple dozen workstations running
>> windows apps in a very subdued manner, i.e. not db intensive at all.
>> How could this happen? I'm tempted to alter the server config and force
>> a max# connections but I'm worried what will happen if I do.
>> Any input/ideas?
>>
>

Connection count gone wild after upgrade to SQL2k5!

We upgraded our server last week to SQL 2005. Prior to this on SQL2k we
averaged 100 connections to the server from our web servers and in-house
applications. All we did was update our application config files so the
connection strings pointed at the new server and now we are up to over
5000!!!!! connections. All of our connection strings are either at the
default max connections or lowered to 50-75. We only have 2 web servers
hitting this SQL box and probably a couple dozen workstations running
windows apps in a very subdued manner, i.e. not db intensive at all.
How could this happen? I'm tempted to alter the server config and force a
max# connections but I'm worried what will happen if I do.
Any input/ideas?How do you count the number of connections?
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23VDKUMIoGHA.4800@.TK2MSFTNGP04.phx.gbl...
> We upgraded our server last week to SQL 2005. Prior to this on SQL2k we
> averaged 100 connections to the server from our web servers and in-house
> applications. All we did was update our application config files so the
> connection strings pointed at the new server and now we are up to over
> 5000!!!!! connections. All of our connection strings are either at the
> default max connections or lowered to 50-75. We only have 2 web servers
> hitting this SQL box and probably a couple dozen workstations running
> windows apps in a very subdued manner, i.e. not db intensive at all.
> How could this happen? I'm tempted to alter the server config and force a
> max# connections but I'm worried what will happen if I do.
> Any input/ideas?
>|||Perfmon - SQLServer:General Statistics -> User Connections
"Remus Rusanu [MSFT]" <remus.rusanu@.online.microsoft.com> wrote in messa
ge
news:uBVNBWIoGHA.3532@.TK2MSFTNGP04.phx.gbl...
> How do you count the number of connections?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23VDKUMIoGHA.4800@.TK2MSFTNGP04.phx.gbl...
>|||Are you using Service Broker activation by any chance? This would include
also features like SqlDependency or Web pages SqlCacheDependency that are
using activation behind the scenes. Activation can cause the perf counter to
go to abnormaly high values, but is only a perfcounter issue (is not
decremented properly).
You can confirm if the connections really grow or is just the a counter
problem by using a management view, like sys.dm_exec_connections.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O8JnogIoGHA.4728@.TK2MSFTNGP03.phx.gbl...
> Perfmon - SQLServer:General Statistics -> User Connections
> "Remus Rusanu [MSFT]" <remus.rusanu@.online.microsoft.com> wrote in mes
sage
> news:uBVNBWIoGHA.3532@.TK2MSFTNGP04.phx.gbl...
>

Connection Broken - SQL 7.0 db migration to SQL 2000

Hi there,
I am trying to upgrade our sql server db from version 7.0
to version 2000. I was successly able to restore the sql
7.0 db into 2000 server. Everything seems to be working
fine except one important stored procedure.
When i execute this particular stored procedure,it works
fine for 2 attempts but crashes the server on the thrid
attempt. The error message i am getting is as follows:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]
ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
It will be great help if someone could shed some light on
this issue.
Thanks,
Savvy.
Hi
Rebuild all your indexes. I have seen this when the statistics get out of
date and you upgrade your DB.
Regards
Mike
"Savvy" wrote:

> Hi there,
> I am trying to upgrade our sql server db from version 7.0
> to version 2000. I was successly able to restore the sql
> 7.0 db into 2000 server. Everything seems to be working
> fine except one important stored procedure.
> When i execute this particular stored procedure,it works
> fine for 2 attempts but crashes the server on the thrid
> attempt. The error message i am getting is as follows:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]
> ConnectionCheckForData (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> It will be great help if someone could shed some light on
> this issue.
> Thanks,
> Savvy.
>

Connection Broken - SQL 7.0 db migration to SQL 2000

Hi there,
I am trying to upgrade our sql server db from version 7.0
to version 2000. I was successly able to restore the sql
7.0 db into 2000 server. Everything seems to be working
fine except one important stored procedure.
When i execute this particular stored procedure,it works
fine for 2 attempts but crashes the server on the thrid
attempt. The error message i am getting is as follows:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]
ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
It will be great help if someone could shed some light on
this issue.
Thanks,
Savvy.Hi
Rebuild all your indexes. I have seen this when the statistics get out of
date and you upgrade your DB.
Regards
Mike
"Savvy" wrote:
> Hi there,
> I am trying to upgrade our sql server db from version 7.0
> to version 2000. I was successly able to restore the sql
> 7.0 db into 2000 server. Everything seems to be working
> fine except one important stored procedure.
> When i execute this particular stored procedure,it works
> fine for 2 attempts but crashes the server on the thrid
> attempt. The error message i am getting is as follows:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]
> ConnectionCheckForData (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> It will be great help if someone could shed some light on
> this issue.
> Thanks,
> Savvy.
>

Connecting to SQL server 7 behind ISA server

Hi,
I recently upgrade a WinNT 4 SP6a Proxy server to Win2K SP4 with ISA SP2. I
have to allow remote users to connect to a SQL server 7 SP4 on Winnt 4 SP6a
behind ISA. The IP is static. It was working fine with Proxy but now I have
the following with clients on the Web:
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets] Network error and if I
try SQL Query Analyzer I have
Unable to connect to server xxx.xxx.xxx.xxx
ODBC:Msg 0, Level 16, State 1
[Microsoft][ODBC SQL Server Driver] Time out error
I have the ISA port 1433 and 1344 (for the apps running with SQL) opened and
listenning . If I run netstat -na on both servers, both show ports as
listenning. I can telnet the ISA external IP on both ports. ISA client is
installed on the SQL server.
I ran thru a similar problem when I configured it for the proxy 2 years ago
and don't remember what was the problem exactly. With the proxy, I had to
add wspclnt.ini in the sql server binn folder to map the port 1433 to the
proxy server and added a connection in SQL server network utility which was
mapped to the external IP of the Proxy. I tried it with and without and
there is no difference. I also remember that I had to use credtool on the
SQL server. Do I have to do it with ISA?
I'm familiar with ISA and i often have to open ports for apps and it works
fine so what is diffenrent for SQL server?
Any help would be greatly appreciated.
Thanks in advance.
Steve Amirault
Take the firewall client off the SQL server and make sure the default
gateway of the SQL server is the ISA server, if that is the route to the
internet. Did you make the Server Publishing Rule for the machine?
Scott Harding
MCSE, MCSA, A+, Network+
Microsoft MVP - Windows NT Server
"Steve" <as@.joe.ca> wrote in message
news:uqKNAmSsEHA.2660@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I recently upgrade a WinNT 4 SP6a Proxy server to Win2K SP4 with ISA SP2.
I
> have to allow remote users to connect to a SQL server 7 SP4 on Winnt 4
SP6a
> behind ISA. The IP is static. It was working fine with Proxy but now I
have
> the following with clients on the Web:
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets] Network error and if I
> try SQL Query Analyzer I have
> Unable to connect to server xxx.xxx.xxx.xxx
> ODBC:Msg 0, Level 16, State 1
> [Microsoft][ODBC SQL Server Driver] Time out error
> I have the ISA port 1433 and 1344 (for the apps running with SQL) opened
and
> listenning . If I run netstat -na on both servers, both show ports as
> listenning. I can telnet the ISA external IP on both ports. ISA client is
> installed on the SQL server.
> I ran thru a similar problem when I configured it for the proxy 2 years
ago
> and don't remember what was the problem exactly. With the proxy, I had to
> add wspclnt.ini in the sql server binn folder to map the port 1433 to the
> proxy server and added a connection in SQL server network utility which
was
> mapped to the external IP of the Proxy. I tried it with and without and
> there is no difference. I also remember that I had to use credtool on the
> SQL server. Do I have to do it with ISA?
> I'm familiar with ISA and i often have to open ports for apps and it works
> fine so what is diffenrent for SQL server?
> Any help would be greatly appreciated.
> Thanks in advance.
> Steve Amirault
>
|||Yes there is a Server rule. Guess what, by doing it the same way I did it
with the former proxy, it works. I disabled the server publishing rule,
added the wspcfg.ini file in the mssql7\binn folder and it works? At the
setup time and according to the situations I had with the ISA, I first
thought that it wouldn't work using the same settings as it was with the
proxy server but it appears that is not the case.
Thanks anyway.
Steve
"Scott Harding - MS MVP" <scrockel@.**NO_SPAM**hotmail.com> a crit dans le
message de news:OJeoP1TsEHA.3200@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Take the firewall client off the SQL server and make sure the default
> gateway of the SQL server is the ISA server, if that is the route to the
> internet. Did you make the Server Publishing Rule for the machine?
> --
> Scott Harding
> MCSE, MCSA, A+, Network+
> Microsoft MVP - Windows NT Server
> "Steve" <as@.joe.ca> wrote in message
> news:uqKNAmSsEHA.2660@.TK2MSFTNGP12.phx.gbl...
SP2.[vbcol=seagreen]
> I
> SP6a
> have
I[vbcol=seagreen]
> and
is[vbcol=seagreen]
> ago
to[vbcol=seagreen]
the[vbcol=seagreen]
> was
the[vbcol=seagreen]
works
>
|||Interesting...you shouldn't have to do that but it depends I think if the
SQL server is a Secure NAT client or not.
Scott Harding
MCSE, MCSA, A+, Network+
Microsoft MVP - Windows NT Server
"Steve" <as@.joe.ca> wrote in message
news:uplh6uUsEHA.3412@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Yes there is a Server rule. Guess what, by doing it the same way I did it
> with the former proxy, it works. I disabled the server publishing rule,
> added the wspcfg.ini file in the mssql7\binn folder and it works? At the
> setup time and according to the situations I had with the ISA, I first
> thought that it wouldn't work using the same settings as it was with the
> proxy server but it appears that is not the case.
> Thanks anyway.
> Steve
>
> "Scott Harding - MS MVP" <scrockel@.**NO_SPAM**hotmail.com> a crit dans le
> message de news:OJeoP1TsEHA.3200@.TK2MSFTNGP09.phx.gbl...
> SP2.
4[vbcol=seagreen]
if[vbcol=seagreen]
> I
opened[vbcol=seagreen]
> is
years[vbcol=seagreen]
> to
> the
which[vbcol=seagreen]
and
> the
> works
>

Connecting to SQL server 7 behind ISA server

Hi,
I recently upgrade a WinNT 4 SP6a Proxy server to Win2K SP4 with ISA SP2. I
have to allow remote users to connect to a SQL server 7 SP4 on Winnt 4 SP6a
behind ISA. The IP is static. It was working fine with Proxy but now I have
the following with clients on the Web:
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets] Network erro
r and if I
try SQL Query Analyzer I have
Unable to connect to server xxx.xxx.xxx.xxx
ODBC:Msg 0, Level 16, State 1
[Microsoft][ODBC SQL Server Driver] Time out error
I have the ISA port 1433 and 1344 (for the apps running with SQL) opened and
listenning . If I run netstat -na on both servers, both show ports as
listenning. I can telnet the ISA external IP on both ports. ISA client is
installed on the SQL server.
I ran thru a similar problem when I configured it for the proxy 2 years ago
and don't remember what was the problem exactly. With the proxy, I had to
add wspclnt.ini in the sql server binn folder to map the port 1433 to the
proxy server and added a connection in SQL server network utility which was
mapped to the external IP of the Proxy. I tried it with and without and
there is no difference. I also remember that I had to use credtool on the
SQL server. Do I have to do it with ISA?
I'm familiar with ISA and i often have to open ports for apps and it works
fine so what is diffenrent for SQL server?
Any help would be greatly appreciated.
Thanks in advance.
Steve AmiraultTake the firewall client off the SQL server and make sure the default
gateway of the SQL server is the ISA server, if that is the route to the
internet. Did you make the Server Publishing Rule for the machine?
Scott Harding
MCSE, MCSA, A+, Network+
Microsoft MVP - Windows NT Server
"Steve" <as@.joe.ca> wrote in message
news:uqKNAmSsEHA.2660@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I recently upgrade a WinNT 4 SP6a Proxy server to Win2K SP4 with ISA SP2.
I
> have to allow remote users to connect to a SQL server 7 SP4 on Winnt 4
SP6a
> behind ISA. The IP is static. It was working fine with Proxy but now I
have
> the following with clients on the Web:
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets] Network er
ror and if I
> try SQL Query Analyzer I have
> Unable to connect to server xxx.xxx.xxx.xxx
> ODBC:Msg 0, Level 16, State 1
> [Microsoft][ODBC SQL Server Driver] Time out error
> I have the ISA port 1433 and 1344 (for the apps running with SQL) opened
and
> listenning . If I run netstat -na on both servers, both show ports as
> listenning. I can telnet the ISA external IP on both ports. ISA client is
> installed on the SQL server.
> I ran thru a similar problem when I configured it for the proxy 2 years
ago
> and don't remember what was the problem exactly. With the proxy, I had to
> add wspclnt.ini in the sql server binn folder to map the port 1433 to the
> proxy server and added a connection in SQL server network utility which
was
> mapped to the external IP of the Proxy. I tried it with and without and
> there is no difference. I also remember that I had to use credtool on the
> SQL server. Do I have to do it with ISA?
> I'm familiar with ISA and i often have to open ports for apps and it works
> fine so what is diffenrent for SQL server?
> Any help would be greatly appreciated.
> Thanks in advance.
> Steve Amirault
>|||Yes there is a Server rule. Guess what, by doing it the same way I did it
with the former proxy, it works. I disabled the server publishing rule,
added the wspcfg.ini file in the mssql7\binn folder and it works? At the
setup time and according to the situations I had with the ISA, I first
thought that it wouldn't work using the same settings as it was with the
proxy server but it appears that is not the case.
Thanks anyway.
Steve
"Scott Harding - MS MVP" <scrockel@.**NO_SPAM**hotmail.com> a crit dans le
message de news:OJeoP1TsEHA.3200@.TK2MSFTNGP09.phx.gbl...
> Take the firewall client off the SQL server and make sure the default
> gateway of the SQL server is the ISA server, if that is the route to the
> internet. Did you make the Server Publishing Rule for the machine?
> --
> Scott Harding
> MCSE, MCSA, A+, Network+
> Microsoft MVP - Windows NT Server
> "Steve" <as@.joe.ca> wrote in message
> news:uqKNAmSsEHA.2660@.TK2MSFTNGP12.phx.gbl...
SP2.[vbcol=seagreen]
> I
> SP6a
> have
I[vbcol=seagreen]
> and
is[vbcol=seagreen]
> ago
to[vbcol=seagreen]
the[vbcol=seagreen]
> was
the[vbcol=seagreen]
works[vbcol=seagreen]
>|||Interesting...you shouldn't have to do that but it depends I think if the
SQL server is a Secure NAT client or not.
Scott Harding
MCSE, MCSA, A+, Network+
Microsoft MVP - Windows NT Server
"Steve" <as@.joe.ca> wrote in message
news:uplh6uUsEHA.3412@.TK2MSFTNGP14.phx.gbl...
> Yes there is a Server rule. Guess what, by doing it the same way I did it
> with the former proxy, it works. I disabled the server publishing rule,
> added the wspcfg.ini file in the mssql7\binn folder and it works? At the
> setup time and according to the situations I had with the ISA, I first
> thought that it wouldn't work using the same settings as it was with the
> proxy server but it appears that is not the case.
> Thanks anyway.
> Steve
>
> "Scott Harding - MS MVP" <scrockel@.**NO_SPAM**hotmail.com> a crit dans le
> message de news:OJeoP1TsEHA.3200@.TK2MSFTNGP09.phx.gbl...
> SP2.
4[vbcol=seagreen]
if[vbcol=seagreen]
> I
opened[vbcol=seagreen]
> is
years[vbcol=seagreen]
> to
> the
which[vbcol=seagreen]
and[vbcol=seagreen]
> the
> works
>