Azure Log Analytics中的SQL死锁通知 [英] SQL deadlock notifications in Azure Log Analytics

问题描述

我有2台SQL服务器和4台应用程序服务器.

I have 2 SQL server and 4 Application servers.

有时我会在该APP服务器的事件日志中得到消息" 事务(进程ID 401)在锁定资源上与另一个进程死锁 并被选为死锁受害者.重新运行事务.用于SQL Server的Microsoft OLE DB提供程序"

And sometimes I got message in the event log of that APP servers  "Transaction (Process ID 401) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.| Microsoft OLE DB Provider for SQL Server"

但是我在Azure中找不到这些通知.

But I can't find those notifications in Azure. May you help me to write correct query?

推荐答案

你好，Alexey，

Hello Alexey,

您是否正在运行Azure SQL数据库?如果是这样，您是否已确认要按照以下说明将诊断日志发送到Log Analytics:

Are you running Azure SQL Databases?  If so, have you confirmed you are sending your diagnostic logs to Log Analytics as outlined here:

https://docs.microsoft.com/zh-cn/azure/sql-database/sql-database-metrics-diag-logging#azure-portal

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-metrics-diag-logging#azure-portal

我看到有一个死锁数据集已发送到Log Analytics:

I see there is a deadlocks dataset which is sent to Log Analytics:

https://docs.microsoft.com/zh-cn/azure/sql-database/sql-database-metrics-diag-logging#deadlocks-dataset

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-metrics-diag-logging#deadlocks-dataset

如果您想查看此类别中的所有内容，则可以使用类似以下的查询:

If you wanted to see everything in this Category, you could use a query like:

AzureDiagnostics |
where Category == "Deadlocks"

如果您要从该查询中获取结果，那么如何优化它可能会变得有意义.

If you are getting back results from this query, it will probably start to make sense how to refine it.

如果要在VM上运行服务，则必须进一步说明如何收集日志.

If you are running your services on VMs, you will have to further explain how you are gathering your logs.

这篇关于Azure Log Analytics中的SQL死锁通知的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！