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:

• Restarted my web site's server (also tried iisreset and restarting the web app)
• Removed all IP filters in Azure portal (including access from Azure services)
• 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)