On a previous blog post written by one of my colleagues it has been discussed the many ways to reach a connection limit in Azure SQL DB.
Recently, we worked on a case where the customer was getting the following error when trying to connect to a SQL database that was part of an elastic pool using SSMS.
In the documentation of the connection limits for the elastic pool we can see that the limit that was reached was “Max concurrent sessions”.
Regardless of the service objective, this session limit is consistent for all tiers. So, scaling up will not provide more sessions.
Fortunately, the customer had an SSMS session where we could execute TSQL queries. We ran the one below under the context of the user database.
SELECT DB_NAME(database_id) as DatabaseName, database_id, count(1) as TotalSessions
FROM sys.dm_exec_sessions es
WHERE es.is_user_process = 1
GROUP BY DB_NAME(database_id), database_id
And here is the output of what we saw
You'll observe that the query revealed to us the enormous number of concurrent sessions that were present in the database with ID 56. The customer application(s) were either not closing them or reusing the connection with some form of connection pooling, thus opening a connection every single time.
The next step was to identify the database. As you can see the DB_NAME function shows only the name for the master database and for the user database where we connected to execute the query.
But at this point is very easy to query sys.databases under the context of master DB and look for the mapping of the database name and the database id.
As soon as the database was identified, we advised our customer to remove it from the pool to avoid connectivity issues with other databases and to terminate any inactive sessions for this database.
When you move a database out of the pool with the portal you can configure the desired service objective.
You can also use Transact-SQL, PowerShell, Azure CLI or REST API to change the service tier.
At that point the issue was fully mitigated, and our customer turned his attention to the application to debug the issue with the connections.
Cheers!