title: More than 100 connections to sql server 2008 in "sleeping" status
body: <p>I have a big trouble here, well at my server.</p>
  
  <p>I have an ASP .net web (framework 4.x) running on my server, all the transactions/select/update/insert are made with ADO.NET.</p>
  
  <p>The 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:</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>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.</p>
  
  <p>It suppose that the connection pooling, keep the connections to re-use them, but until I see don't re-use them always.</p>
  
  <p>Any idea besides check for close all the connections open after use them?</p>
  
  <p><strong>SOLVED</strong>(now I have just one and beautiful connection on "sleeping" status):</p>
  
  <p>Besides the anwser of <strong>David Stratton</strong>, I would like to share this link that help explain really well how the connection pool it works: <strong><a href="http://dinesql.blogspot.com/2010/07/sql-server-sleeping-status-and.html" rel="noreferrer">http://dinesql.blogspot.com/2010/07/sql-server-sleeping-status-and.html</a></strong></p>
  
  <p>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.</p>
  
  <p>Be careful with dataReaders you should close its connection to (that was what make me mad for while).</p>