Sql-server – Extended Events session file truncated on server restart

extended-eventssql serversql server 2014

I have created an extended events session to capture login and logout information. The session is setup to store events to a file.
I understood that one of the advantages of using an event file (rather than a ring buffer) was that events history would be persistent across a restart.
However it appears that a new event file is created when SQL Server is restarted and the history is lost.
Is this expected? If so, what is the advantage of using a file rather than a ring buffer?

Best Answer

Based on your question (and additional comments regarding your setting of max_rollover_files to 1:

However it appears that a new event file is created when SQL Server is restarted and the history is lost. Is this expected?

Yes - that is expected. When you restarted SQL Server all currently running extended events were terminated. When you restarted SQL Server, a new extended event session started and your existing target file was replaced because you indicated you only wanted 1 file. If you want to preserve extended event data stored in target files across server restarts, you will have to increase the max_rollover_files to something greater than 1.

sys.fn_xe_file_target_read_file (path, mdpath, initial_file_name, initial_offset) has the capability of reading all extended event files in a the path referenced by mdpath if you specify null for the initial_file_name, so maintaining multple files should not be a problem.

Regarding the use of the Ring Buffer, take a look at why Jonathan Kehayias (world renowned expert in SQL Server Extended Events) Hates the ring buffer.

Related Solutions

Sql-server – Querying Extended Events in SQL Server 2008

Try to play with this example:

CREATE EVENT SESSION
[statement_completed_file]
ON SERVER
ADD EVENT
    sqlserver.sp_statement_completed,
ADD EVENT
    sqlserver.sql_statement_completed
    (
        ACTION 
        (
            sqlserver.sql_text
        )
        WHERE
        (
            sqlserver.session_id <> 53
        )
    )
ADD TARGET package0.asynchronous_file_target
    (
        set filename=N'C:\stats.xel', 
        metadatafile=N'C:\stats.xem'
    )
WITH
(
    MAX_MEMORY=4096KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=1 SECONDS,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=OFF
)
GO
ALTER EVENT SESSION [statement_completed_file]
ON SERVER
STATE=START
GO
SELECT event_data, 
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    event_data.value('(event/data/value)[1]', 'bigint') AS source_database_id,
    event_data.value('(event/data/value)[2]', 'bigint') AS object_id,
    event_data.value('(event/data/value)[3]', 'bigint') AS object_type,
    event_data.value('(event/data/value)[4]', 'bigint') AS cpu,
    event_data.value('(event/data/value)[5]', 'bigint') AS duration,
    event_data.value('(event/data/value)[6]', 'bigint') AS reads,
    event_data.value('(event/data/value)[7]', 'bigint') AS writes,
    event_data.value('(event/action/value)[1]', 'nvarchar(max)') AS sql_text
FROM 
(
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('C:\stats*.xel', 'C:\stats*.xem', null, null)
) tbl
GO
DROP EVENT SESSION [statement_completed_file] ON SERVER
GO

Sql-server – Collecting parameter values from Extended event session

Someone mentioned "I think only sqlserver.rpc_completed event will give you complete SQL command including parameter values." Please review answer below:

Capture Passed Parameter Values In Sql Server Extended Sessions

Best Answer

Related Solutions

Sql-server – Querying Extended Events in SQL Server 2008

Sql-server – Collecting parameter values from Extended event session

Related Question