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.

Answer 3

If those connections are still hung and not timed out, you can use t-sql KILL command to kill them.

Another option is to use DAC . See details here on MSDN.

If none of these options help, please email me details of your server and DB on shantanu dot kurhekar at microsoft dot com and I can help.

Answer 4

You can use DAC admin connection similar to the sql on premise and kill the connections when ran out of sessions. You can find details @ http://www.sqlindepth.com/2015/05/diagnostic-connections-to-sql-db-v12-databases/

Answer 5

Another lesser known option here, that limit is based on which tier you are on (S1, S2, P1, etc.) So you could move up a tier to get a higher login amount that potentially would have allowed you to resolve the issue.

Often enough moving up a tier like that will also move which node you are on which would remove the errant logins also.