Reputation: 7512

Sql Server 2005 Connection Limit

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.

Upvotes: 1

Answers (4)

6eorge Jetson

Reputation: 2235

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>

Upvotes: 2

Joe Ratzer

Reputation: 18549

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...

Upvotes: 4

Ken

Reputation: 2092

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.

Upvotes: 0

MusiGenesis

Reputation: 75296

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.

Upvotes: 3

Sql Server 2005 Connection Limit

Answers (4)

RETURN @ResultVar

Related Questions