Sql-server – Extended event not staying on

extended-eventssql serverssmsstored-procedurest-sql

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]([sqlserver].[database_id],(4)) 
            AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) 
            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]([sqlserver].[database_id],(4)) 
            AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) 
            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]([sqlserver].[database_id],(4)) 
            AND [package0].[equal_boolean]([sqlserver].[is_system],(0))     
            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=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=ON,
    STARTUP_STATE=ON
)

GO

Edit
I started it when I set it up, and it captured data for two days (8/9 – 8/11). Haven't checked in again until today, found it was off and there has been no data since 8/11. It is currently on and capturing data, but I don't want to have to manually check that it's on every day.

Best Answer

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.

Related Question