Sql Server 2005 Connection Limit...

Question

Is there a connection limit on Sql Server 2005 Developers Edition. We have many threads grabbing connections, and I know ADO.NET does connection pooling, but I get OutOfMemory exceptions. We take out the db connections and it works fine.

Answer 1

You may not be closing or disposing of your connection objects correctly. Make sure your code looks something like this:

using (SqlConnection conn = new SqlConnection("connectionstring"))
{
    conn.Open();

    // database access code goes here
}

The using block will automatically close and dispose of your connection object.

Answer 2

Are the out of memory exceptions from the .NET? If the error was on the server you would probably see a connection refused message instead.

Answer 3

This is the response to that question on Euan Garden's (a Program Manager for Visual Studio Team Edition) blog:

There are no limits in terms of memory, db size or procs for DE, it is essentially Enterprise Edition. There is however a licensing restriction that prevents it from being used in production.

Therefore, you probably just need to make sure you are closing your connection objects properly. The using block will be perfect for such a job...

Answer 4

32767 on Enterprise Edition

<ServerProductVersion>9.00.3235.00</ServerProductVersion>
<ServerProductLevel>SP2</ServerProductLevel>
<ServerEdition>Enterprise Edition</ServerEdition>
<ServerEngineEdition>3</ServerEngineEdition>

How I check...

CREATE FUNCTION [dbo].svfV1GetSessionAndServerEnvironmentMetaData RETURNS xml AS BEGIN

-- Declare the return variable here
DECLARE @ResultVar xml

-- Add the T-SQL statements to compute the return value here
SET @ResultVar =
 (
  SELECT  
   @@SPID           as SPID,
   @@ProcID          as ProcId,
   @@DBTS           as DBTS,
   getdate()          as DateTimeStamp,
   System_User          as SystemUser,
   Current_User         as CurrentUser,
   Session_User         as SessionUser,
   User_Name()          as UserName,
   Permissions()         as UserSessionPermissionsBitmap,
   Host_Id()          as HostId,
   Host_Name()          as HostName,
   App_Name()          as AppName,

   ServerProperty('ProcessId')      as ServerProcessId,
   ServerProperty('MachineName')     as ServerMachineName,
   ServerProperty('ServerName')     as ServerServerName,
   ServerProperty('ComputerNamePhysicalNetBIOS') as ServerComputerNamePhysicalNetBIOS,
   ServerProperty('InstanceName')     as ServerInstanceName,
   ServerProperty('ProductVersion')    as ServerProductVersion,
   ServerProperty('ProductLevel')     as ServerProductLevel,

   @@CONNECTIONS         as CumulativeSqlConnectionsSinceStartup,
   @@TOTAL_ERRORS         as CumulativeDiskWriteErrorsSinceStartup,
   @@PACKET_ERRORS         as CumulativeNetworkPacketErrorsSinceStartup,

   --Note:  
   --If the time returned in @@CPU_BUSY, or @@IO_BUSY exceeds approximately 49 days of cumulative CPU time, 
   --you receive an arithmetic overflow warning. In that case, 
   --the value of @@CPU_BUSY, @@IO_BUSY and @@IDLE variables are not accurate. 
  -- @@CPU_BUSY * @@TIMETICKS      as CumulativeMicroSecondsServerCpuBusyTimeSinceStartup,
  -- @@IO_BUSY * @@TIMETICKS       as CumulativeMicroSecondsServerIoBusyTimeSinceStartup,
  -- @@IDLE * @@TIMETICKS       as CumulativeMicroSecondsServerIdleTimeSinceStartup,

   ServerProperty('BuildClrVersion')    as ServerBuildClrVersion,
   ServerProperty('Collation')      as ServerCollation,
   ServerProperty('CollationID')     as ServerCollationId,
   ServerProperty('ComparisonStyle')    as ServerComparisonStyle,
   ServerProperty('Edition')      as ServerEdition,
   ServerProperty('EditionID')      as ServerEditionID,
   ServerProperty('EngineEdition')     as ServerEngineEdition,
   ServerProperty('IsClustered')     as ServerIsClustered,
   ServerProperty('IsFullTextInstalled')   as ServerIsFullTextInstalled,
   ServerProperty('IsIntegratedSecurityOnly')  as ServerIsIntegratedSecurityOnly,
   ServerProperty('IsSingleUser')     as ServerIsSingleUser,
   ServerProperty('LCID')       as ServerLCID,
   ServerProperty('LicenseType')     as ServerLicenseType,
   ServerProperty('NumLicenses')     as ServerNumLicenses,
   ServerProperty('ResourceLastUpdateDateTime') as ServerResourceLastUpdateDateTime,
   ServerProperty('ResourceVersion')    as ServerResourceVersion,
   ServerProperty('SqlCharSet')     as ServerSqlCharSet,
   ServerProperty('SqlCharSetName')    as ServerSqlCharSetName,
   ServerProperty('SqlSortOrder')     as ServerSqlSortOrder,
   ServerProperty('SqlSortOrderName')    as ServerSqlSortOrderName,

   @@MAX_CONNECTIONS        as MaxAllowedConcurrentSqlConnections,

   SessionProperty('ANSI_NULLS')     as SessionANSI_NULLS,
   SessionProperty('ANSI_PADDING')     as SessionANSI_PADDING,
   SessionProperty('ANSI_WARNINGS')    as SessionANSI_WARNINGS,
   SessionProperty('ARITHABORT')     as SessionARITHABORT,
   SessionProperty('CONCAT_NULL_YIELDS_NULL')  as SessionCONCAT_NULL_YIELDS_NULL,
   SessionProperty('NUMERIC_ROUNDABORT')   as SessionNUMERIC_ROUNDABORT,
   SessionProperty('QUOTED_IDENTIFIER')   as SessionQUOTED_IDENTIFIER
  FOR XML PATH('SequenceIdEnvironment')
 ) 
-- Return the result of the function

RETURN @ResultVar

END

on my SQL Server database engine instance returns

<SequenceIdEnvironment>
  <SPID>56</SPID>
  <ProcId>1666821000</ProcId>
  <DBTS>AAAAAAAAB9A=</DBTS>
  <DateTimeStamp>2008-10-02T15:09:26.560</DateTimeStamp> ...
  <CurrentUser>dbo</CurrentUser>
  <SessionUser>dbo</SessionUser>
  <UserName>dbo</UserName>
  <UserSessionPermissionsBitmap>67044350</UserSessionPermissionsBitmap>
  <HostId>3852 </HostId> ...
  <AppName>Microsoft SQL Server Management Studio - Query</AppName>
  <ServerProcessId>508</ServerProcessId> ...
  <ServerProductVersion>9.00.3235.00</ServerProductVersion>
  <ServerProductLevel>SP2</ServerProductLevel>
  <CumulativeSqlConnectionsSinceStartup>169394</CumulativeSqlConnectionsSinceStartup>
  <CumulativeDiskWriteErrorsSinceStartup>0</CumulativeDiskWriteErrorsSinceStartup>
  <CumulativeNetworkPacketErrorsSinceStartup>0</CumulativeNetworkPacketErrorsSinceStartup>
  <ServerBuildClrVersion>v2.0.50727</ServerBuildClrVersion>
  <ServerCollation>SQL_Latin1_General_CP1_CI_AS</ServerCollation>
  <ServerCollationId>872468488</ServerCollationId>
  <ServerComparisonStyle>196609</ServerComparisonStyle>
  <ServerEdition>Enterprise Edition</ServerEdition> ...
  <ServerEngineEdition>3</ServerEngineEdition>
  <ServerIsClustered>0</ServerIsClustered>
  <ServerIsFullTextInstalled>1</ServerIsFullTextInstalled>
  <ServerIsIntegratedSecurityOnly>0</ServerIsIntegratedSecurityOnly>
  <ServerIsSingleUser>0</ServerIsSingleUser> ...
  <ServerResourceLastUpdateDateTime>2008-03-12T18:59:08.633</ServerResourceLastUpdateDateTime>
  <ServerResourceVersion>9.00.3235</ServerResourceVersion>
  <ServerSqlCharSet>1</ServerSqlCharSet>
  <ServerSqlCharSetName>iso_1</ServerSqlCharSetName>
  <ServerSqlSortOrder>52</ServerSqlSortOrder>
  <ServerSqlSortOrderName>nocase_iso</ServerSqlSortOrderName> **
  <MaxAllowedConcurrentSqlConnections>32767</MaxAllowedConcurrentSqlConnections> **
  <SessionANSI_NULLS>1</SessionANSI_NULLS>
  <SessionANSI_PADDING>1</SessionANSI_PADDING>
  <SessionANSI_WARNINGS>1</SessionANSI_WARNINGS>
  <SessionARITHABORT>1</SessionARITHABORT>
  <SessionCONCAT_NULL_YIELDS_NULL>1</SessionCONCAT_NULL_YIELDS_NULL>
  <SessionNUMERIC_ROUNDABORT>0</SessionNUMERIC_ROUNDABORT>
  <SessionQUOTED_IDENTIFIER>1</SessionQUOTED_IDENTIFIER>
</SequenceIdEnvironment>