How to clear SQL Server Extended Events Event File

Question

How to clear SQL Server extended events that are stored in a file?

Background

Where are the files for SQL Server Extended Events Event File target stored?

I want to delete months worth of log files; but SQL Server doesn't tell me where the files are:

I would follow the advice on Microsoft's SQL Server forums:

Clear events in file target?

Simply stop the session and delete .xel file if it’s no longer required.

Attempt#3

I tried doing the single most obvious thing that any user interface designer worth their salt would have created from the beginning: Right-click the event file target, and select:

• Clear
• Delete
• Purge
• Empty

Except there is no option to do any of those obvious things:

Attempt#4

I also tried going into the Extended Events menu, and clicking Clear Data. But the option is inexplicably disabled:

Attempt#5

I also tried to script the Extended Events Session, in order to see where it is storing the files. But of course SQL Server team is not helpful:

ADD TARGET package0.event_file(SET filename=N'Expensive Queries',max_file_size=(25),max_rollover_files=(4)),

Attempt#6

In SQL Server Profiler. If you wanted to clear the events you pushed the button to clear the events:

SQL Profiler is deprecated, and it's replacement provides no way to clear the events.

---

What is the way to clear the events?

Bonus Reading

• Query to clear sql server logs over a certain age
• Clear events in file target?
• BOL: Targets for Extended Events in SQL Server
• BOL: Event File Target

Answer 1

1. Detect event file file path with a query like this

SELECT CAST(t.target_data AS XML).value('(EventFileTarget/File/@name)[1]', 'VARCHAR(MAX)') FilePathAndName
FROM sys.dm_xe_sessions s WITH (NOLOCK)
JOIN sys.dm_xe_session_targets t WITH (NOLOCK) ON s.address = t.event_session_address
WHERE t.target_name = 'event_file'
    AND s.name = 'tempdbgrowth' -- <-- replace tempdbgrowth with extended event name

2. Delete the file (or files) from file system

3. SSMS right-click on extended-event (higher level then event file) and select "Refresh"

Answer 2

As is mentioned above, event file is in C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Log\ - so you can delete it from disk (in case you have access and permission to do it). In case you have no acces/permission to SQL server or file (from any reason), you can do it from SQL Management studio.

In this case, you can remove current file and add (create) new file again. It can be done with or without stopping event. Path : Properties -> Data Storage

Open "Properties":

Go to "Data Storage" and remove file

Add new file:

Answer 3

Here's how you get the file path:

SELECT n.value('(@name)[1]','varchar(255)') AS FilePath
FROM 
(
    SELECT CAST(t.target_data AS XML) target_data
    FROM sys.dm_xe_sessions s
        INNER JOIN sys.dm_xe_session_targets t ON t.event_session_address = s.address
    WHERE s.name = 'Your Session Name'
        AND t.target_name = N'event_file'
) AS tab
    CROSS APPLY [target_data].[nodes]('EventFileTarget/File') AS [q] ([n]);

The only way I've found to clear the data is to drop the session delete the files and then create the session again.

Answer 4

You can also use

DECLARE @SQLDataRoot VARCHAR(400)
EXEC master..xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE', 
 @key = 'SOFTWARE\Microsoft\MSSQLServer\Setup', 
 @value_name = 'SQLDataRoot', @value = @SQLDataRoot OUTPUT
 SELECT @SQLDataRoot 

Answer 5

By default the path seems like it would be

C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Log\*.xel

or basically wherever the system files are kept for SQL Server (i.e. the default ERRORLOG location). If there is nothing there then it may be that your Extended Event is set to ring buffer in which case only the latest information is kept and it's stored in memory. Seems the only way to clear the log in this case would be to stop and start the session.