Deadlock all of us know the meaning of it and how to manage it on SQL Server on-premises and for how to get alert by it normally we are using Database Mail to generate alert from SQL Server but in Azure, the situation is different because there is no Database Mail in Azure there are services called “Azure Monitor” centralized services for managing all of the alerts on all of the Azure resources and this in of the difference between SQL on Azure and SQL on-premises. From this service we can manage our alerts with defining what is the action we need it (Send Email, Execute Function, Call Services, Send SMS ..etc.), You can access the Monitoring Services from this ➡ link or simply by searching azure portal by (Monitor).

  • Azure Alert Component
  • How to create Deadlocks Alert
  • List by alerts created on Azure
  • Alert Dashboard
  • How to Get the deadlock reports Using Azure Matrices
  • How to check Deadlock using SQL Server Script
  • Other Articles For How to Manage Azure SQL Database

IF you need to check all of my azure posts ➡ Latest Microsoft Azure Articles and Posts

Azure Alert Component

  • Resource: The Azure Resource you need to apply for this Alert role on It and it can be done on the azure subscription itself like alert on cost and budget of the subscription.
  • Condition: it is the logic of the alert what you need to monitor exactly and when
  • Action Group: the action you need to do it when the alert fire on the condition you configure it
  • Matrices: It is like dashboard and reports you can configure it to get some statistics on time and you can build alert on it
  • ALERT DETAILS: alert name and alert description and severity for this alert from (0) to (4)

How to create Deadlocks Alert

  • Open Azure Portal and open Monitor Services ➡ link
  • In the left side select ➡ Alert and click on New Alert Rule
  • Select the Resource by doing filter on a resource type (SQL Databases)
  • Then select your SQL Server database you need to monitor the deadlock on it.
  • Configure the condition(signal logic) we have two types of single logic matrices and Activity log
  • Deadlocks one of the matrices for Azure SQL select it
  • In the same page configure the other options like (Alert threshold) when the alert will fire 🧨
  • Create New action group by selecting which action you need it to be executed when the alert fired “Action group can be selected in multiple alerts so if you already created action group for sending an email, for example, no need to create another one use this action group on all of the alerts you need to send an email by it”
  • Now the alert is created and in case of any deadlock on this database you should get email

During the Alert Create may be you get this massage (The subscription is not registered to Microsoft.insights resource provider) To solve it check this ➡ Post

List by alerts created on Azure

  • Open Azure Portal and open Monitor Services ➡ link
  • On the left side select ➡ Alert and click on Manage Alert Rule.

Alert Dashboard

  • Open Azure Portal and open Monitor Services ➡ link
  • In the left side select ➡ Overview, you will find count by enabled alerts and the alert fired
  • IF you click on Row of the alert fired or on Total Alerts you will get the list by alert details on time
  • IF you click on your alert you can change the status of it from New to Acknowledge or closed

How to Get the deadlock reports Using Azure Matrices

  • Open Azure Portal and open Monitor Services ➡ link
  • In the left side select ➡ Matrices
  • Select the Scope (Services you need to get a report on it)
  • In the matrices type select Deadlocks

Any matrices you configure it you can build alert on it from the top by selecting a new alert rule

How to check Deadlock using SQL Server Script

Open Azure SQL Database using Management studio we can’t use Query editor on Azure SQL Database because the Query editor doesn’t support connecting to the master database and the below DMV Should be Executed on Master DB.

use master
GO
SELECT * FROM sys.event_log
WHERE event_type = 'deadlock'

Or you can generate the Deadlocks as XML then converting it to Graphical report by saving the XML code with extension. XDL  Then opening it again using SQL Server Management Studio.

WITH CTE AS (
SELECT CAST(event_data AS XML) AS [target_data_XML]
FROM sys.fn_xe_telemetry_blob_target_read_file('dl', null, null, null)
)
SELECT target_data_XML.value('(/event/@timestamp)[1]', 'DateTime2') AS Timestamp,
target_data_XML.query('/event/data[@name=''xml_report'']/value/deadlock') AS
deadlock_xml,
target_data_XML.query('/event/data[@name=''database_name'']/value').value('(/value)[1]',
'nvarchar(100)') AS db_name
FROM CTE
order by Timestamp desc

Finally, if you check your email you should get an email like below

Other Articles For How to Manage Azure SQL Database

Keep Following Me

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.