Sql-server – Can’t start system_health extended events session

extended-eventssql serversql server 2014

On some SQL Server instances, (I did not find a pattern yet), when I try to manually start this session, I get the following error :

For target,
"5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file", the
parameter "filename" passed is invalid. Target parameter at index 0 is
invalid (Microsoft SQL Server, Error: 25641)

If we look into the SQL Server logs, each time the instance starts (i.e. after Windows reboots for patches to get applied), we get :

Error: 25641, Severity: 16, State: 0. For target,
"5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file", the
parameter "filename" passed is invalid. Target parameter at index 0 is
invalid

Error: 25710, Severity: 16, State: 1. Event session
"system_health" failed to start. Refer to previous errors in the
current session to identify the cause, and correct any associated
problems.

Error: 25709, Severity: 16, State: 1. One or more event
sessions failed to start. Refer to previous errors in the current
session to identify the cause, and correct any associated problems.

Has anyone observed this issue ? I suspect it's due to permissions, as the definition of the session does not indicate a path, but simply the name of the .xel file. That is, it writes directly to the SQL Server logs folder, where obviously the SQL Server service account is granted full access.

I get the same issue for the AlwaysOn_health session.

Thanks in advance for your help !

Best Answer

First, identify the folder that is the problem. In SSMS, with results set to grid, run the following query:

SELECT target_data = CONVERT(xml, target_data) 
  FROM sys.dm_xe_session_targets
  WHERE target_package_guid = '5B2DA06D-898A-43C8-9309-39BBBE93EBBD'
  AND target_name = N'event_file';

Should return one row, and you should be able to click on it from the grid result. This will open a new tab with an XML document, which should look something like this:

<EventFileTarget truncated="0">
  <Buffers logged="0" dropped="0" />
  <File name="C:\...path_to_xe_files...\system_health_0_131278486394000000.xel" />
</EventFileTarget>

If the file name does not include a path component, the Extended Events default file location can be determined by looking in the system registry. This code should do that:

EXEC master.sys.xp_instance_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE'
       , @key          = N'SOFTWARE\Microsoft\MSSQLServer\CPE'
       , @value_name   = N'ErrorDumpDir';

Once you have the path where the Extended Events files are being saved, try to do something like this:

BACKUP DATABASE model TO DISK = 'C:\...path_to_xe_files...\model_trash.bak'
  WITH INIT;

This should give you a better idea of what the problem is - could be permissions, could be that the folder doesn't exist (maybe the configuration for the instance was moved), could be a whole slew of things. But this should point you in the right direction and get you a better error message.

Related Question