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

Jacobba

Amen! Why can't we have a better way of clearing than having to go onto the server and delete a danged file? – Chumley

Answer 1

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.

Protege

By default an admin user does not even have rights to access the Logs directory, so searching in a higher level directory for your .xel file will return no hits, even if it exists. – Byzantine

Answer 2

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 

Updo

For me this returned directory C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL. Searching that directory for *.xel returned no hits. Attempting to open the nested Logs directory revealed that I did not have the rights to even view the directory. Giving myself rights to access Logs showed that it was however full of .xel files including the one for my event session. Got to hand it to MS, they sure know how to make the simplest thing an insufferable ballache, 45 minutes work to clear an event log. Impressive! – Byzantine

Answer 3

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"

Fallacious

This query only returned a result for me while the Extended Events session was running. Once I stopped the session the query didn't return any result, although the file still existed. Still the most useful answer here as other answers assume the file will be included in the SQL Server directory, rather than showing where the file actually is (in my case the person setting up the session had explicitly specified the full path to the file, with a non-standard location). – Blate

Answer 4

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.

Ripplet

You can usually just stop the session which unlocks the file, delete the file, start again. – Banquer

Answer 5

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:

Impersonal