Default event_file path for Extended Events

Foreword

Have you ever wondered where the .xel file is saved when you create a new Extended Event session and don’t specify the full path (just the file name)?

Like so:

Well, so did I and here’s what I’ve found out.

Test

Let’s run our tests on a local instance because we’ll have to restart it at some point.

CREATE EVENT SESSION [TestFileTarget] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed
ADD TARGET package0.event_file
(
    SET filename=N'TestFileTarget',max_file_size=(2)
)
WITH (STARTUP_STATE = ON)

I created the session via GUI and then scripted it out. You can see that even the scripted version contains only the file name.

We have to start the session if we want to find out through TSQL where the file is saved.

ALTER EVENT SESSION [TestFileTarget] ON SERVER
STATE = START

That populates the sys.dm_xe_sessions, DMV and we can find the current location with this snippet.

; -- Previous statement must be properly terminated
WITH xeTargets
AS
(
    SELECT
        s.name
        , t.target_name
        , CAST(t.target_data AS xml) AS xmlData
    FROM
        sys.dm_xe_session_targets AS t
        JOIN sys.dm_xe_sessions AS s
            ON s.address = t.event_session_address
)
SELECT
    xt.name
    , xt.target_name
    , xNodes.xNode.value('@name', 'varchar(250)') AS filePath
    , xt.xmlData
FROM xeTargets AS xt
CROSS APPLY xt.xmlData.nodes('.//File') xNodes (xNode) /* OUTER APPLY if you want to see other sessions */

This is my result; your path will be different:

Let’s look into that folder (use your favourite explorer or cmd line).

Get-ChildItem -Path "D:SqlServerMSSQL16.MSSQLSERVERMSSQLLog" | Select-Object Name

I can see there are several files:

• errorlog
• errorlog.1
• HkEngineEventFile_0_133143177576200000.xel
• log.trc
• system_health_0_133143177578450000.xel
• TestFileTarget_0_133143178669430000.xel

It seems like it’s in the same folder as the errorlog. So let’s change the errorlog’s path and see if XE event files are also affected.

The error log’s path is a SQL Server startup parameter. And this is the relevant excerpt from the documentation (emphasis mine).

Is the fully qualified path for the error log file (typically, C:Program FilesMicrosoft SQL ServerMSSQL.nMSSQLLOGERRORLOG).

If you do not provide this option, the existing registry parameters are used.

To change the startup parameter, I’ll use Configuration Manager.

Right-click the SQL Server service, select Properties and then go to the Startup Parameters tab.

Let’s change the folder to something else. But remember to provide the errorlog filename (without extension) at the end. Otherwise, the instance will not start!

I’m changing it to -eD:ErrorLogErrorlog, a new folder I’ve created.

This change requires SQL Server service restart, so let’s do that as well.

Since we’ve specified the WITH (STARTUP_STATE = ON), the event is running. So we can check the path with the previously mentioned snippet.

But if we check the old errorlog folder, we can see that the old error log and XE files are still there. This is because they are not cleaned up automatically.

As you would expect, reading from the XE file shows only the rows stored in the new location, so be mindful of that.

Bonus test

If you don’t have the SQL Server installed locally, don’t worry. We can test it even faster in Docker. I’m using the latest image mcr.microsoft.com/mssql/server:2022-latest but feel free to use a different one if you already have it locally.

docker run `
-e 'ACCEPT_EULA=Y' `
-e 'SA_PASSWORD=Password5' `
-e 'MSSQL_PID=Developer' `
-p 14338:1433 `
-d `
--name xefilepath `
mcr.microsoft.com/mssql/server:2022-latest

I’ll get this result by running the first few steps from the earlier demo (creating and starting the session + finding the file location).

That’s the default error log path on Linux. But I can change that with an environment variable.

Let’s remove the container and recreate it with the variable MSSQL_ERROR_LOG_FILE (again, remember to specify the errorlog file without an extension at the end).

docker rm -f xefilepath

docker run `
-e 'ACCEPT_EULA=Y' `
-e 'SA_PASSWORD=Password5' `
-e 'MSSQL_PID=Developer' `
-e MSSQL_ERROR_LOG_FILE='/var/opt/mssql/dontblink/errorlog' `
-p 14338:1433 `
-d `
--name xefilepath `
mcr.microsoft.com/mssql/server:2022-latest

Since we removed the container, we have to recreate the session again.

When we do that, we can see that the Extended Event file is created in the expected folder.

Thank you for reading.