Unable to start deadlock Extendeds events on Azure SQL Database

Question

whenever i create deadlock Extendeds events with storage as eventfile as below,Extendeds events gets created in stopped state.When i start the extended event with below command its giving the followig error:

ALTER EVENT SESSION [dba_deadlocks] ON SERVER STATE = START;

error:

Msg 25737, Level 16, State 1, Line 10 Database scoped extended event sessions are not available in server scope or system databases in Azure DB.

When i start Xtended event from gui i am seeing below error: The target, "5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file", encountered a configuration error during initialization. Object cannot be added to the event session. (null) (.Net SqlClient Data Provider)

---

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-25602-database-engine-error

---

Server Name: testsql.database.windows.net Error Number: 25602 Severity: 16 State: 1 Line Number: 1

---

Xtended event created with below code:

CREATE EVENT SESSION [dba_deadlocks] ON DATABASE ADD EVENT sqlserver.database_xml_deadlock_report ADD TARGET package0.event_file(SET filename=N'https://testingaccount.blob.core.windows.net/dbatestingscripts/deadlocks.xel') WITH (STARTUP_STATE=ON) GO

I am able create deadlock Extendeds events successfully on Azure SQL Database (database name is testdb) with out any storage options but not with storage event_file

Let me know if there is anyotherway to caputre deadlocks to a container in Azure.

Answer 1

You need to configure the extended event session at the database level, on the database you created and not on the master database as shown below:

Before you can configure the extended event session, you should do the following steps:

1. Setup a storage account V2 General Purpose

2. Create a container, get its path and get a Shared Access Signature

3. On the Azure SQL DB create a master encryption key.

4. Create credentials in the database to access the storage account