SQL Server allows a maximum of 32,767 user connections. In this tutorial we’ll show you how to find out the number of connections to a database in SQL Server, and restrict the number of connections to a SQL Server instance.

Part 1: View Total Number of Connections in SQL Server

Need to check the number of active connections for each database? This can be done easily using the following script. The script displays the databaseName, the number of connections and the login name:

SELECT DB_NAME(dbid) as DBName,
loginame,
COUNT(dbid) as NumberOfConnections
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

If you’re running SQL Server 2012, 2014 or 2016, run this script instead:

SELECT DB_NAME(database_id) as DBName,
login_name,
COUNT(session_id) as NumberOfConnections
FROM sys.dm_exec_sessions
GROUP BY database_id, login_name

Part 2: Restrict Concurrent Database Connections in SQL Server

To configure the maximum user connections, open SQL Server Management Studio and connect to your database, then follow these steps:

1. In Object Explorer, right-click a server and click Properties.

   

2. Click the Connections node on the left page.
3. Under Connections, in the Maximum number of concurrent connections box, type or select a value from 0 through 32767 to set the maximum number of users that are allowed to connect simultaneously to the instance of SQL Server.

   

4. Click OK. Restart SQL Server to apply your changes.