如果无法访问Azure SQL数据库，该如何终止对它的连接?

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

今天我们迁移到AzureSQL V12。今晚我的网站离线了，因为有一个持续的问题:

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)

资源ID:3。数据库的登录限制是90，已经达到。见“http://go.microsoft.com/fwlink/?LinkId = 267637”寻求帮助。(Microsoft SQL Server, Error: 10928)

I've tried the following:

我试过以下:

Restarted my web site's server (also tried iisreset and restarting the web app)
重新启动我的web站点的服务器(也尝试了iisreset和重新启动web应用程序)
Removed all IP filters in Azure portal (including access from Azure services)
删除Azure portal中的所有IP过滤器(包括对Azure服务的访问)
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)
升级到Azure的下一层(它停留在“进行中”而没有改变，所以我猜这种连接阻碍了高端)

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.

我无法通过SSMS连接到我的数据库。我得到了相同的错误信息。这已经持续了几个小时了，我的站点完全离线了，但是登录的次数并没有改变。

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

我需要一些方法来断开这些连接，这样我就可以对问题进行诊断和诊断。

5 个解决方案

#1

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

要查看Azure SQL DB上的现有连接，我使用以下查询:

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:

为了杀死除我的(SPID)以外的所有连接，我使用这个查询:

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.

如果这些连接仍然挂起并且没有超时，您可以使用t-sql KILL命令来杀死它们。

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

另一个选择是使用DAC。请参阅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/

您可以在前提下使用与sql类似的DAC管理连接，并在会话耗尽时终止连接。您可以在http://www.sqlindepth.com/2015/05/diagnostics -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.

另一个不太为人所知的选项是，该限制基于您所在的层(S1、S2、P1等)，因此您可以向上移动一层以获得更高的登录数量，这可能会使您能够解决问题。

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.

我一开始还不知道是什么原因造成的，但是我们已经从Web版迁移到标准S0层，然后将数据库从V11升级到V12，我认为在这个过程中出了问题。

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

我确实喜欢下面的建议来尝试DAC，如果问题再次发生，我会给它一个去报告。

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.

更新2:为了防止其他人感兴趣，在我看来，根据我从微软收到的信息，自动备份有一个问题，这个问题在某种程度上是失控的，没有删除数据库的连接。如果我听到更多，我将发布更新，但同时我建议，最好在升级/更改SQL Azure实例的层之前禁用备份作业。

#1