SQL Server数据库级是否存在最大连接数限制？

Great question! Let's break this down clearly:

• SQL Server does NOT have a built-in, direct connection limit at the database level — all connections are counted against the instance-wide max_connections setting (which defaults to 0, translating to the practical upper limit of 32,767 you mentioned). No matter which database a connection targets, it consumes one of the instance's available connection slots.

That said, if you need to restrict connections to a specific database, there are practical workarounds you can implement:

• Login Triggers: You can create a server-level trigger that fires when a user logs in. It can check which database the user is attempting to connect to, count active connections to that database, and terminate the new connection if your threshold is exceeded. Here's a simplified example snippet:

  CREATE TRIGGER RestrictTargetDBConnections
  ON ALL SERVER WITH EXECUTE AS 'sa'
  FOR LOGON
  AS
  BEGIN
      DECLARE @TargetDB NVARCHAR(128) = 'YourRestrictedDB';
      DECLARE @CurrentDB NVARCHAR(128) = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(128)');
      DECLARE @ActiveConnCount INT;
  
      SELECT @ActiveConnCount = COUNT(*)
      FROM sys.dm_exec_sessions
      WHERE database_id = DB_ID(@TargetDB) AND is_user_process = 1;
  
      IF @CurrentDB = @TargetDB AND @ActiveConnCount > 50
      BEGIN
          RAISERROR('Maximum connections to YourRestrictedDB exceeded.', 16, 1);
          ROLLBACK;
      END
  END;
  

• Resource Governor: For more enterprise-grade control, you can use Resource Governor to create a workload group linked to your target database, then set explicit limits on the maximum number of sessions for that group. This approach also lets you regulate other resources like CPU and memory alongside connections.

It's worth noting that any issues that feel like a database-level connection limit are usually related to resource constraints (like insufficient memory to spin up new sessions) or permission restrictions (a user lacking access to connect to the database), not a built-in connection cap at the database level.

内容的提问来源于stack exchange，提问作者andrewthedev