2

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.

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.