More than 100 connection to sql server 2008 in "sleeping" status - Solved
- by Allende
I have a big trouble here, well at my server.
I have an ASP .net web (framework 4.x) running on muy server, all the transactions/select/update/insert are made with ADO.NET.
Well my problem is that after being using for a while (a couple of updates/selects/inserts) sometimes I got more than 100 connections on "sleeping" status when check for the connections on sql server with this query:
SELECT
spid,
a.status,
hostname,
program_name,
cmd,
cpu,
physical_io,
blocked,
b.name,
loginame
FROM
master.dbo.sysprocesses a INNER JOIN
master.dbo.sysdatabases b ON
a.dbid = b.dbid where program_name like '%TMS%'
ORDER BY spid
I've been checking my code and closing every time I make a connection, I'm gonna test the new class, but I'm afraid the problem doesn't be fixed.
It suppose that the connection pooling, keep the connections to re-use them, but until I see don't re-use them always.
Any idea besides check for close all the connections open after use them?
SOLVED(now I have just one and beautiful connection on "sleeping" status):
Besides the anwser of David Stratton, I would like to share this link that help explain really well how the connection pool it works: http://dinesql.blogspot.com/2010/07/sql-server-sleeping-status-and.html
Just to be short, you need to close every connection (sql connection objects) in order that the connection pool can re-use the connection and use the same connectinos string, to ensure this is highly recommended use one of the webConfig.
Be careful with dataReaders you sould close its connection to (that was what make got out of my mind for while).
