CODEWITHSUNDEEP
azureazure-sql-databaseazure-storageextended-events
DBdev
DBdev

Reputation: 53

Extended events in Azure - Object cannot be added to the event session

I am trying to create Extended Events on an Azure SQL DB. I have followed the instructions of the below Azure documents:

http://andreas-wolter.com/en/1804-tracing-with-extended-events-in-azure-sql-database/

https://blogs.msdn.microsoft.com/azuresqldbsupport/2018/03/13/extended-events-capture-step-by-step-walkthgrough/

I am successfully created Extended events session. When I tried to start my session, I am getting below error message .

> TITLE: Microsoft SQL Server Management Studio

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

ADDITIONAL INFORMATION:

The target, "5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file", encountered a configuration error during initialization. Object cannot be added to the event session. The operating system returned error 5: 'Access is denied. ' while creating the file 'https://xxxxxxxxxxxxxxxx.blob.core.windows.net/Container/FileName_0_132028925297100000.xel'. (Microsoft SQL Server, Error: 25602)

Please note that I have created container using azure portal and I am able to upload files from portal.

Please suggest me to resolve the error.

Upvotes: 1

Views: 1883

Answers (3)

SujaiSparks
SujaiSparks

Reputation: 73

We can also configure this using SAS key identity. Here is a template that I use for troubleshooting blocks and deadlocks in azure sql database..

Credits: https://sqland.wordpress.com/2022/10/04/how-to-monitor-blocked-processes-in-azure-sql-db/

IF EXISTS (SELECT * FROM sys.database_scoped_credentials WHERE name = 'https://myazurestorageaccount.blob.core.windows.net/extended-events-log')
DROP DATABASE SCOPED CREDENTIAL [https://myazurestorageaccount.blob.core.windows.net/extended-events-log]
GO

CREATE DATABASE SCOPED CREDENTIAL [https://myazurestorageaccount.blob.core.windows.net/extended-events-log]
WITH IDENTITY='SHARED ACCESS SIGNATURE'
 --Remove ? mark from the SAS token.
 --Ensure the expiry datetime of the SAS token covers the duration of the extended events log collection time
 , SECRET = 'sv=2022-11-02&ss=bfqt&srt=sco&sp=rwdlacupiytfx&se=2023-12-30T20:39:42Z&st=2023-08-23T12:39:42Z&spr=https&sig=Abcdef%1ghijkl%2mnopq%3rstuvwxyz%4sujai%3D'
GO

IF EXISTS(SELECT 'true' FROM sys.database_event_sessions WHERE name = 'my_XE_Trace')
DROP EVENT SESSION [my_XE_Trace] ON DATABASE 
Go

CREATE EVENT SESSION [my_XE_Trace] ON DATABASE 
ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.query_hash,sqlserver.request_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.tsql_stack)
    WHERE ([sqlserver].[database_name]=N'my_database_name')), 
ADD EVENT sqlserver.database_xml_deadlock_report(
    ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.query_hash,sqlserver.request_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.tsql_stack)
    WHERE ([sqlserver].[database_name]=N'my_database_name')), 

--Capturing locks that were acquired for more than 5 seconds
ADD EVENT sqlserver.lock_acquired(
    ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.query_hash,sqlserver.request_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.tsql_stack)
    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'my_database_name') 
                AND [duration]>=(5000000))), 
ADD EVENT sqlserver.lock_deadlock_chain(
    ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.query_hash,sqlserver.request_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence)
    WHERE ([sqlserver].[database_name]=N'my_database_name')), 

--Capturing sp_statement_completed events where is more than 5 seconds
ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1)
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence)
    WHERE (((([package0].[greater_than_uint64]([sqlserver].[database_id],(4))) AND ([package0].[equal_boolean]([sqlserver].[is_system],(0)))) 
                AND ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'my_database_name'))) 
                AND ([duration]>(5000000)))), 

--Capturing sql_statement_completed events where is more than 5 seconds
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence)
    WHERE (((([package0].[greater_than_uint64]([sqlserver].[database_id],(4))) AND ([package0].[equal_boolean]([sqlserver].[is_system],(0)))) 
                AND ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'my_database_name'))) 
                AND ([duration]>(5000000)))),

ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[database_name]=N'my_database_name')),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[database_name]=N'my_database_name'))
ADD TARGET package0.event_file(SET filename=N'https://myazurestorageaccount.blob.core.windows.net/extended-events-log/LoadTest20230817/LoadTest20230817.xel',max_file_size=(10240))
WITH (STARTUP_STATE=OFF)
GO

SELECT * FROM sys.database_event_session_events
GO

--Read from a .xel file created on the blob storage
SELECT distinct object_name FROM sys.fn_xe_file_target_read_file
(N'https://myazurestorageaccount.blob.core.windows.net/extended-events-log/LoadTest20230817/LoadTest20230817_0_133373392162450000.xel', 
null, null, null) ;
GO

--Read only the blocked_process_report events from a .xel file created on the blob storage
SELECT * FROM sys.fn_xe_file_target_read_file
(N'https://myazurestorageaccount.blob.core.windows.net/extended-events-log/LoadTest20230817/LoadTest20230817_0_133373392162450000.xel', 
null, null, null)
Where object_name = 'blocked_process_report';
GO

Upvotes: 0

NP007
NP007

Reputation: 688

As Per my past experience , I also faced the same issue. And that cause because of case sensitive of Storage URL or container name. Please validate again with your URL. Might be it resolve your problem.

Upvotes: 1

Alberto Morillo
Alberto Morillo

Reputation: 15668

Try to change the target of the Extended Event to the ring buffer instead:

    ADD TARGET package0.ring_buffer -- Store events in the ring buffer target
    (SET MAX_MEMORY = 4096, max_events_limit = 5000)
WITH (MAX_DISPATCH_LATENCY = 120 SECONDS,
      STARTUP_STATE = ON);

Here you will find how to read the ring buffer.

Upvotes: 0

Related Questions