By default, SQL Server dynamically manages the memory needed for user connections. However, the maximum number of connections can be set to a fixed value by setting the user connections configuration option to a value other than 0. Setting the user connections option to a non-zero value is not recommended. If this option is set to a non-zero value and the specified number of connections is exceeded, any additional login attempts will fail with the above message. If the value is set to 1, the SQL Server instance may not start.
By default, SQL Server dynamically manages the memory needed for user connections. However, the maximum number of connections can be set to a fixed value by setting the user connections configuration option to a value other than 0. Setting the user connections option to a non-zero value is not recommended. If this option is set to a non-zero value and the specified number of connections is exceeded, any additional login attempts will fail with the above message. If the value is set to 1, the SQL Server instance may not start.
SQL Server is running and you have an existing system administrator connection to it, issue the following commands to reset the user connection option to the default value of 0 (dynamic):
sp_configure 'user connections', 0
go
reconfigure with override
The SQL Server instance will need to be restarted for the change to take effect.
If SQL Server will not start or if you are unable to use one of the existing connections, you will need to start the SQL Server instance in a minimal configuration and reset the user connection option to the default value of 0 (dynamic).
If the instance is clustered, make sure that SQL Server and SQL Server Agent are offline and that the node you are working from owns the SQL Server disk resources.
Stop or take offline MS DTC as well as any other application or service that can connect to this SQL Server instance.
Use the following syntax to start SQL Server from a command prompt (you must switch to the binn directory for this instance of SQL Server):
SQL Server default instance or SQL Server 7.0: sqlservr.exe -c -f
SQL Server named instance: sqlservr.exe -c -f -s {instancename}
Use SQL Server Management Studio to connect to SQL Server. Make sure to use a logon profile that has System Administrator permissions on SQL Server.
Issue the following commands:
sp_configure 'user connections', 0
go
reconfigure with override
In the command prompt window, press CTRL+C to quit SQL Server. Type Y to shut down SQL Server.
Start SQL Server normally.
Name | Description | Default Value |
Enabled | Enables or disables the workflow. | Yes |
Interval (seconds) | The recurring interval of time in seconds in which to run the workflow. | 300 |
Priority | Defines Alert Priority. | 1 |
Severity | Defines Alert Severity. | 1 |
Synchronization Time | The synchronization time specified by using a 24-hour format. May be omitted. |
|
Timeout (seconds) | Specifies the time the workflow is allowed to run before being closed and marked as failed. | 200 |
Timeout for query execution (seconds) | The workflow will fail and register an event, if the query execution takes longer than the specified period. | 60 |
Timeout for database connection (seconds) | The workflow will fail and register an event, if it cannot access the database during the specified period. | 15 |
Target | Microsoft.SQLServer.Linux.DBEngine | ||
Category | EventCollection | ||
Enabled | True | ||
Alert Generate | True | ||
Alert Severity | Warning | ||
Alert Priority | Normal | ||
Remotable | True | ||
Alert Message |
| ||
Comment | Mom2017ID='{A9344B04-ED2A-4F54-B91D-CC99D35469F1}';MOM2017GroupID={467ECC75-C5DA-42BD-955C-A73BBB51AF74} |
ID | Module Type | TypeId | RunAs |
---|---|---|---|
_F6DA1507_12AF_11D3_AB21_00A0C98620CE_ | DataSource | Microsoft.SQLServer.Linux.DataSource.EventCollectionFiltered | Default |
GenerateAlert | WriteAction | System.Health.GenerateAlert | Default |
<Rule ID="Microsoft.SQLServer.Linux.EventRule.DBEngine.Maximum_limit_for_connections_has_been_reached_1_5_Rule" Target="SqlDiscL!Microsoft.SQLServer.Linux.DBEngine" Enabled="true" ConfirmDelivery="true" Remotable="true" Comment="Mom2017ID='{A9344B04-ED2A-4F54-B91D-CC99D35469F1}';MOM2017GroupID={467ECC75-C5DA-42BD-955C-A73BBB51AF74}">
<Category>EventCollection</Category>
<DataSources>
<DataSource ID="_F6DA1507_12AF_11D3_AB21_00A0C98620CE_" Comment="{F6DA1507-12AF-11D3-AB21-00A0C98620CE}" TypeID="Microsoft.SQLServer.Linux.DataSource.EventCollectionFiltered">
<MachineName>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/MachineName$</MachineName>
<NetbiosComputerName>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/NetbiosComputerName$</NetbiosComputerName>
<InstanceName>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/InstanceName$</InstanceName>
<ConnectionString>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/ConnectionString$</ConnectionString>
<InstanceVersion>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Version$</InstanceVersion>
<InstanceEdition>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Edition$</InstanceEdition>
<SqlExecTimeoutSeconds>60</SqlExecTimeoutSeconds>
<SqlTimeoutSeconds>15</SqlTimeoutSeconds>
<TimeoutSeconds>200</TimeoutSeconds>
<IntervalSeconds>300</IntervalSeconds>
<SyncTime/>
<EventDisplayNumber>17809</EventDisplayNumber>
</DataSource>
</DataSources>
<WriteActions>
<WriteAction ID="GenerateAlert" TypeID="Health!System.Health.GenerateAlert">
<Priority>1</Priority>
<Severity>1</Severity>
<AlertMessageId>$MPElement[Name="Microsoft.SQLServer.Linux.EventRule.DBEngine.Maximum_limit_for_connections_has_been_reached_1_5_Rule.AlertMessage"]$</AlertMessageId>
<AlertParameters>
<AlertParameter1>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/MachineName$</AlertParameter1>
<AlertParameter2>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/InstanceName$</AlertParameter2>
<AlertParameter3>Event ID: $Data/Property[@Name='EventID']$. $Data/Property[@Name='Message']$</AlertParameter3>
</AlertParameters>
<Suppression>
<SuppressionValue/>
</Suppression>
</WriteAction>
</WriteActions>
</Rule>