Lesson Learned #336: Using STARTUP_STATE option in an extended event in Azure SQL Database

[Jose_Manuel_Jurado]

We got a service request that our customer reported that after a planned maintenance the extended event that they created Lesson Learned #1: Capturing a TSQL Command Timeout - Microsoft Community Hub is not longer captures the events.

In this situation, we saw that the Azure SQL Database was scaled up and our customer is not using the parameter called STARTUP_STATE , so, our customer needs to start it manually. CREATE EVENT SESSION (Transact-SQL) - SQL Server | Microsoft Learn

CREATE EVENT SESSION ssEventoTimeout 
ON DATABASE 
ADD EVENT sqlserver.sql_batch_completed ( 
ACTION  (sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username) 
WHERE ([result] <> (0))) 
ADD TARGET package0.asynchronous_file_target( 
SET filename='https://storageaccount.blob.core.windows.net/extended/Data.xel')
with( STARTUP_STATE = ON)

To test this one, I changed the definition of my extended event including STARTUP_STATE = ON. After scaling my database I was able to see this extended event running again running the following query: SELECT * FROM SYS.dm_xe_database_session_events.

Enjoy!