How do I kill connections to Azure SQL database if

问题:

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.

回答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)

回答2:

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.

回答3:

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/

回答4:

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.

回答5:

RESOLUTION: In the end, after several hours on the phone to Microsoft, they were unable to access the server by conventional means and had to migrate the database to a different node before the connections cleared.

I still have no idea what caused this in the first place, but we'd migrated from Web Edition to Standard S0 tier and then upgraded the database from V11 to V12, and I think something went awry along the way.

I do like the suggestions below to try out DAC, and if the problem reoccurs I will give it a go and report back.

UPDATE 2: Just in case anyone else is interested, at this time it looks to me, based on info I received from Microsoft, that there was a problem with the automated backups which somehow went haywire and didn't drop connections to the database. If I ever hear more, I'll post an update, but in the meantime I would suggest that it might be prudent to disable any backup jobs you might have prior to upgrading/changing tiers of your SQL Azure instance.