Recently I noticed one interesting issue in our production server. We have an app server (IIS and asp .net framework) and a DB server (SQL Server 2008) installed.
During peak time when I try to telnet my DB server on 1433 it keeps getting failed but sometimes it gets connected. Both these servers are blade server and are in same chassis. No network firewall is in between all the two servers.
Also in application logs its shows error like
A network-related or instance-specific error occurred while establishing a connection to SQL Server.
The server was not found or was not accessible.
Verify that the instance name is correct and that SQL Server is configured to allow remote ...
Provider, error: 40 - Could not open a connection to SQL Server
I checked number of connections on my DB using the command
SELECT COUNT(dbid) as TotalConnections
FROM sys.sysprocesses
WHERE
dbid > 0
it returns approximately 140-150 connections while we have multiple databases (~100) on it.
I will appreciate if anybody can help me in troubleshooting this issue.
We are making connection strings as <add name="DBNAME" connectionString="Data Source=10.X.X.X;Initial Catalog=DBNAME;User ID=sa;Password=test" providerName="System.Data.SqlClient" />
Also in web config we have <sessionState timeout="10" />
