Managed Instance diagnostic events do not support sending deadlock information to Log Analytics. However, through auditing, it's possible to query failed queries along with their reported error messages—though this does not include deadlock XML.
We will see how we can send information to Log Analytics and setup an alert for when a deadlock occurs.
Step 1 - Deploy Log Analytics
Create a Log Analytics workspace if you currently don't have one
Create a Log Analytics workspace
Step 2 - Add diagnostic setting
On the Azure Portal, open the Diagnostic settings of your Azure SQL Managed Instance and choose Add diagnostic setting
Select SQL Security Audit Event and choose has destination your Log Analytics workspace
Step 3 - Create a server audit on the Azure SQL Managed Instance
Run the query below on the Managed Instance
Rename the server audit and server audit specification to a name of your choice.
CREATE SERVER AUDIT [audittest] TO EXTERNAL_MONITOR
GO
-- we are adding Login audit, but only BATCH_COMPLETED_GROUP is necessary for query execution
CREATE SERVER AUDIT SPECIFICATION audit_server FOR SERVER AUDIT audittest
ADD (SUCCESSFUL_LOGIN_GROUP), ~
ADD (BATCH_COMPLETED_GROUP),
ADD (FAILED_LOGIN_GROUP) WITH (STATE = ON)
GO
ALTER SERVER AUDIT [audittest] WITH (STATE = ON)
GO
Step 4 - Check events on Log Analytics
It may take some time for records to begin appearing in Log Analytics.
Open your Log Analytics workspace and choose Logs
To verify if data is being ingested, run the following query in Log Analytics and wait until you start getting the first results:
Make sure that you change servername with your Azure SQL Managed Instance name
AzureDiagnostics
| where LogicalServerName_s == "servername"
| where Category == "SQLSecurityAuditEvents"
| take 10Example:
Step 5 - (Optional) Create a deadlock event for testing
Create a deadlock scenario so you can see a record on log analytics.
Example:
Open SSMS and a new query window under the context of a user database (you can create a test database just for this test).
create a table on a user database and insert 10 records:
create table tb1 (id int identity(1,1) primary key clustered, col1 varchar(30))
go
insert into tb1 values ('aaaaaaa')
go 10You can close the query window or reuse for the next step.
Open a new query window (or reuse the first query window) and run (leave the query window open after executing):
begin transaction
update tb1 set col1 = 'bbbb' where id = 1Open a second query window and run (leave the query window open after executing):
begin transaction
update tb1 set col1 = 'bbbb' where id = 2Go back to the first query window opened and run (the query will be blocked - will stay executing):
update tb1 set col1 = 'bbbb' where id = 2Go back to the second query window opened and run (this transaction will be victim of deadlock):
update tb1 set col1 = 'bbbb' where id = 1You can rollback and close all windows after the deadlock exception.
Step 6 - (Optional) Check the deadlock exception on Log Analytics
Note: the record can take some minutes to appear on Log Analytics
Use the query below to obtain the Deadlock events for the last hour (we are looking for Error 1205)
Make sure that you change servername with your Azure SQL Managed Instance name
AzureDiagnostics
| where TimeGenerated > ago(1h)
| where LogicalServerName_s == "servername"
| where Category == "SQLSecurityAuditEvents"
| where succeeded_s == "false"
| where additional_information_s contains "Err 1205, Level 13"
Step 7 - Use query to Create an Alert
Use the query below to create an Alert on Azure Log Analytics
Make sure that you change servername with your Azure SQL Managed Instance name. The query checks for deadlocks that occurred on the previous hour.
AzureDiagnostics
| where TimeGenerated > ago(1h)
| where LogicalServerName_s == "servername"
| where Category == "SQLSecurityAuditEvents"
| where succeeded_s == "false"
| where additional_information_s contains "Err 1205, Level 13"Run the query and click on New alert rule
Create the alert with the desired settings
Microsoft