How do I kill connections to Azure SQL database if I can't access it?

Question

Today we migrated to AzureSQL V12. Tonight my site is offline, because of a persistent issue with the following message:

Resource ID : 3. The LoginLimit limit for the database is 90 and has been reached. See 'http://go.microsoft.com/fwlink/?LinkId=267637' for assistance. (Microsoft SQL Server, Error: 10928)

I've tried the following:

1. Restarted my web site's server (also tried iisreset and restarting the web app)
2. Removed all IP filters in Azure portal (including access from Azure services)
3. Upscaled to the next tier in Azure (it is stuck on "In Progress" and not changing, so I guess the connections are preventing the upscale)

I can't connect to my database via SSMS. I get the same error message. This has lasted for hours now, and my site is completely offline, yet the number of logins is not changing.

I need some way to disconnect some of these connections so I can get on and diagnose what the issue might be.

Answer 1

To see existing connections on Azure SQL DB I use this query:

SELECT
    c.session_id, c.net_transport, c.encrypt_option,
    s.status,
    c.auth_scheme, s.host_name, s.program_name,
    s.client_interface_name, s.login_name, s.nt_domain,
    s.nt_user_name, s.original_login_name, c.connect_time,
    s.login_time
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
--WHERE c.session_id = @@SPID;
--WHERE status = 'sleeping'
ORDER BY c.connect_time ASC

To KILL all connections except mine (SPID) I use this query:

DECLARE @kill varchar(8000) = '';

SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(5), c.session_id) + ';'

FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id <> @@SPID
--WHERE status = 'sleeping'
ORDER BY c.connect_time ASC

EXEC(@kill)

Answer 2

This issue was caused by Azure automated backups failing to end sessions correctly. None of the other answers worked as they all require you to be able to connect to the database, and I was unable to do this. I ended up speaking on the phone to Microsoft Support for some hours, during which they were also unable to connect to the database for the same reason.

The resolution, from their end, was to migrate the database to a new node, which is not something that Azure users can do, so if you encounter this level of issue, really the best (and only) thing to do is to contact Microsoft support ASAP.

I do recommend trying out some of the other suggestions here first, but without the ability to make any connections you will be stuck.

Because this occurred during the process of upgrading a database on Azure, we implemented a procedure of disabling automated backups before upgrading databases as a precaution, and the problem has not reoccurred for us.