Deadlock Collection Fails Due to Missing .xel File (4379013)

The DBSS_Deadlock_Data collection fails because the SQL Server extended event (.xel) file cannot be found on the monitored host.

This error means SQL Server tried to read a file that wasn't there. The file helps track deadlocks (when two processes block each other). Because the file was missing, SQL Server gave a serious error called Error 823. This error is like a warning that something is wrong with the system's ability to read files, and it could affect the database's health. It's important to check the system and make sure everything is working properly.

An error similar to the following appears in the SQL Server agent log:

ERROR [AGENTNAME-lowPriorityPool-2-[DBSS_Deadlock_Data][]]
com.quest.qsi.fason.core.collection.processor.InstanceProcessor - 
Failed to run DBSSDeadlockExtendedEventsProcessor processor of DBSS_Deadlock_Data collection.
java.lang.RuntimeException: Failed to execute collection [DBSS_Deadlock_Data], 
reason=The operating system returned error 2(The system cannot find the file specified.) 
while reading from the file '{SQLSERVERPATH}\MSSQL\Log\system_health_0_124849413452550000.xel'.

In some environments, this issue may also trigger a SQL Server Error 823 in the SQL Server Error Log. This error indicates a severe I/O failure when SQL Server attempts to read a missing or inaccessible file. If this occurs, it may appear as:

Error: 823, Severity: 24, State: 5
"The operating system returned error 2 (The system cannot find the file specified.) 
to SQL Server during a read..."

Cause 1 – Cleanup of Old .xel Files

A manual or scheduled process may have removed older extended event files from the SQL Server log directory.

Cause 2 – Ring Buffer and File Target Rollover Behavior

The system_health session uses both a ring_buffer (in-memory) and file target with rollover. If the number of files is limited (e.g., 4 files), older .xel files are deleted automatically.

If no filenames are returned, the session may be configured to only use the ring_buffer, which stores data in memory only. Once stopped, the buffer is cleared.

For more information:

• Microsoft Docs – Ring Buffer Target
• SQLSkills Blog – Extended Events Ring Buffer

Cause 3 – Modified Directory Path or Filename Pattern

The default file target path or filename pattern in the system_health session may have been changed, resulting in the agent referencing a non-existent path.

Cause 4 – Insufficient Permissions

The Foglight SQL Server agent service account may lack file-level read permissions for the .xel file or its directory.

Cause 5 – File Corruption or Disk Issues

A corrupted file or disk issue may have removed or made the file unreadable.

WORKAROUND

Change the deadlock working mode to Trace to collect deadlock data using the legacy method. See KB 4228897 for details.

STATUS

Resolution 1 – Check Cleanup Jobs or Scripts

Review maintenance plans, cleanup jobs, or third-party tools that might be removing .xel files too aggressively.

Resolution 2 – Verify Session Configuration

Check if the system_health session exists and review its configuration:

SELECT * 
FROM sys.server_event_sessions 
WHERE name = 'system_health';    

Then, check the file target settings to ensure the session is writing to disk and not only using in-memory ring_buffer:

SELECT s.name, t.target_name, CAST(t.target_data AS XML) AS target_data
FROM sys.server_event_sessions s
JOIN sys.server_event_session_targets t ON s.event_session_id = t.event_session_id
WHERE s.name = 'system_health';    

This will show the file path, rollover settings, and whether the session is configured to save data to disk. If needed, adjust the max_file_size or max_rollover_files to retain more history.

Resolution 3 – Check File Target Configuration

Confirm the file target path and settings:

SELECT t.target_name, c.value AS target_data
FROM sys.server_event_sessions s
JOIN sys.server_event_session_targets t ON s.event_session_id = t.event_session_id
CROSS APPLY sys.fn_xe_file_target_read_file(t.target_data, NULL, NULL, NULL) AS c
WHERE s.name = 'system_health';

If this issue occurs frequently, increase the max_file_size or max_rollover_files in the session configuration.

Resolution 4 – Verify File Access Permissions

Ensure that the SQL Server agent’s service account has proper read access to the directory containing .xel files.

Resolution 5 – Check for Errors in Logs

Review the SQL Server error logs and the Windows Event Viewer for relevant file access or disk errors.

Other Recommendations

A) Restart the system_health Session

If files are missing or unreadable, restart the session to regenerate:

ALTER EVENT SESSION system_health ON SERVER STATE = STOP;
ALTER EVENT SESSION system_health ON SERVER STATE = START;

B) Confirm File Existence

Verify that .xel files are present in the expected directory:

dir "{SQL Server path}\MSSQL\Log\*.xel"