SQL Server 2005连接限制
在Sql Server 2005 Developers Edition上是否有连接限制。我们有许多线程捕获连接，并且我知道ADO.NET可以进行连接池，但是我收到OutOfMemory异常。我们取出数据库连接，它工作正常。
解决方案
我们可能没有正确关闭或者处置连接对象。确保代码看起来像这样：
using (SqlConnection conn = new SqlConnection("connectionstring"))
{
conn.Open();
// database access code goes here
}
using块将自动关闭并处置连接对象。
.NET是否存在内存不足异常？如果错误出在服务器上，我们可能会看到连接被拒绝的消息。
这是Euan Garden(Visual Studio Team Edition的程序经理)博客上对这个问题的回答：
DE的内存，数据库大小或者procs没有限制，它实质上是企业版。
但是，存在许可限制，无法将其用于生产中。
因此，我们可能只需要确保正确关闭了连接对象即可。 using块将非常适合此类工作...
企业版上为32767
<ServerProductVersion> 9.00.3235.00 </ ServerProductVersion>
<ServerProductLevel> SP2 </ ServerProductLevel>
<ServerEdition>企业版</ ServerEdition>
<ServerEngineEdition> 3 </ ServerEngineEdition>
我如何检查...
创建函数[dbo] .svfV1GetSessionAndServerEnvironmentMetaData
返回xml
作为
开始
-- 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
返回@ResultVar
结尾
在我的SQL Server数据库引擎实例上返回
<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查询</ 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>企业版</ 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>
