Extended event not staying on

I've created an extended event to debug a very rare issue in a web application. Very occasionally data from forms is not making it to a stored procedure, so I want to verify that when the SP is being called, it is actually lacking the missing data. I'm using to Extended Event to view the passed parameters.

I went in today to check the logs, and found that the Event has not been capturing data, and indeed, the session was stopped in SSMS.

How do I ensure this even doesn't turn itself off? I've set it to start on server startup. Here is text of the event:

```
CREATE EVENT SESSION [DB_Name SP execution] ON SERVER

ADD EVENT sqlserver.rpc_starting
(
ACTION
(
sqlserver.database_id,sqlserver.database_name,
sqlserver.server_instance_name,
sqlserver.session_id
)
WHERE
(
[package0].greater_than_uint64)
AND [package0].equal_boolean)
AND [sqlserver].[database_id]=(34))
),

ADD EVENT sqlserver.sp_statement_starting
(
SET collect_statement=(1)
ACTION
(
sqlserver.database_id,sqlserver.database_name,
sqlserver.server_instance_name,
sqlserver.session_id
)
WHERE
(
[package0].greater_than_uint64)
AND [package0].equal_boolean)
AND [sqlserver].[database_id]=(34)
)
),

ADD EVENT sqlserver.sql_batch_starting
(
ACTION
(
sqlserver.database_id,sqlserver.database_name,
sqlserver.server_instance_name,
sqlserver.session_id
)
WHERE
(
[package0].greater_than_uint64)
AND [package0].equal_boolean)
AND [sqlserver].[database_id]=(34)
)
)

ADD TARGET package0.event_file
(
SET filename=N'D:\path\to\log.xel',
max_file_size=(5120)
)
WITH
(
MAX_MEMORY=409

Since both ALTER EVENT SESSION and DROP EVENT SESSION are DDL statements that have Server Scope, I would suggest setting up a Server DDL Trigger to capture when your EE is stopped/dropped.

This generates a simple notification when either DDL statement is executed, but feel free to alter it to your liking:

CREATE TRIGGER EE_Change_Notifications
ON ALL SERVER
FOR ALTER_EVENT_SESSION, DROP_EVENT_SESSION
AS
    DECLARE @msgBody NVARCHAR(MAX)

    SET @msgBody = N'Extended Event [' + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(256)') + N']' + CHAR(13) + CHAR(10)
            + N'Event Type: [' + EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(256)') + N']' + CHAR(13) + CHAR(10)
            + N'Login: [' + EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(256)') + N']' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
            + N'Statment:' + CHAR(13) + CHAR(10) + EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')

    EXEC msdb..sp_send_dbmail
          @profile_name = 'DB Mail Profile Name'
        , @recipients = 'person@place.com'
        , @body = @msgBody
GO

If this doesn't provide notification that the EE has been stopped, you may be running into a situation where the event is failing for whatever reason. At that point review your ERROR logs and see if anything occurred and dig from there. If you can narrow down a specific error code, you could then configure a SQL Agent Alert to email you if/when that specific error happens again.

StackExchange Database Administrators Q#184177, answer score: 4