Query on Metrics, Blocked by Firewall, Deadlocks and Failed connection

Question

We are having few queries on the below metrics .

1)Blocked by Firewall

How to get the IP address and the request details which is blocked by the firewall

2)Deadlocks

How to get the deadlocks details

3)Failed connections

How to get the failed connection details

PS - Based on common issues that we have seen from customers and other sources; we are posting these questions to help the Azure community.

Answer 1

Greetings!

1)Blocked by Firewall: How to get the IP address and the request details which is blocked by the firewall

 Azure SQL Database does not directly provide the IP addresses and request details that are blocked by the firewall. However, you can use auditing features to get information about failed connection attempts.

 Enable Auditing: Azure SQL Database Auditing can audit server-level and database-level firewall changes. You can enable it to track and log events associated with your database.

 Refer to the link for more about auditing:https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-overview?view=azuresql

2)Deadlocks: How to get the deadlocks details

 The T-SQL queries below provided deadlock details.

The following query returns a list of recent deadlocks for all databases on the server:

The deadlock_xml column contains the relevant information, including the T-SQL statements, processes, tables, locks, and indexes that were involved in the deadlock.

To view the deadlock graph:

• Save the content from the deadlock_xml column into an .xdl format, for example, deadlock.xdl.
• Open the .xdl file in SSMS to analyze the deadlock graph.

Refer to the below document for more details regarding deadlock event:

[Lesson Learned #19: How to obtain the deadlocks of your Azure SQL Database or Managed Instance? - Microsoft Community Hub]

[Analyze and prevent deadlocks - Azure SQL Database | Microsoft Learn]

3)Failed connections: How to get the failed connection details 

 

To get the details of failed connections in Azure SQL Database, you can use the sys.event_log and sys.database_connection_stats views.

 Here are the steps:

 sys.event_log: This view contains logs of all events including connection failures. You can run the following query on master database to get details of failed connections:

 SELECT * FROM sys.event_log WHERE event_type = 'connection_failed' ORDER BY start_time DESC  The above query will give you a list of all failed connection attempts, ordered by the time they occurred.

Regards

Geetha