1

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.

DBdev
  • 53
  • 6

3 Answers3

1

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.

NP007
  • 659
  • 8
  • 21
  • Awesome man! I found that container name is case sensitive in URL which I passed to store event_file, I corrected it and now it works for me. Thanks a lot! – DBdev May 22 '19 at 08:59
0

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.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
  • Thanks for your response. In my current project our end goal is to store data in event_file and use it for further analysis. – DBdev May 22 '19 at 05:36
0

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
SujaiSparks
  • 73
  • 1
  • 5