It should come as no surprise that I write a lot of articles about Extended Events (XE). This happens to be another article on Extended Events. Truth be told, this article is hopefully something that is more of an edge case scenario. Well, I sure hope that is the case and that it is not a common problem.
One of the recommended methods to trap payload data in an XE session is via the use of the event_file target. Sending data to a file has numerous benefits such as being able to take the trace and evaluate the trace file from a different machine (locally to that machine).
Every once in a blue moon you just may run into various issues with the event_file such as explained here or here. Though slightly different, the net effect is quite similar and should be treated with roughly the same kind of troubleshooting steps.
Configuration Error
As luck would have it, I ran into one of these rare opportunities to troubleshoot an error occurring on a client server. Truth be told, I was unfamiliar with the actual error at first. Here is that error.
Error: 25602, Severity: 17, State: 22.
The preceding error was scraped out of the SQL Server error log. Obviously a little more detail was needed because this error is far from useful without that detail. Looking a little deeper, I found some errors like this.
Msg 25602, Level 17, State 22, Line 43
The target, “5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file”,
encountered a configuration error during initialization. Object cannot be added to the event session.
Some very good clues are actually contained in that particular message. Some of these clues include the following: a) the term “target”, b) the term “event_file”, and c) the phrase “event session.” Ok, I get it at this point. One of my Extended Event Sessions I had put on the server and used previously was broken. But, since it had been working and I know I had fetched data from it, I found myself puzzled as to why it might be busted.
The next logical thing to do at this point was to test the various sessions that are stopped and try to figure out which one is causing the problem and see if the error is reproduced. Finally upon finding the session that is failing, I ran into the complete message.
Msg 25602, Level 17, State 22, Line 43
The target, “5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file”,
encountered a configuration error during initialization. Object cannot be added to the event session.
The operating system returned error 3: ‘The system cannot find the path specified.
‘ while creating the file ‘C:\Database\XEDROPME\SVRLoginAudit_0_131650006658030000.xel’.
The additional info that I needed is in bold text in the previous text. So, for some reason, there is a problem with the path for the XE trace file output. Going out to the file system to check it out, I found that the client in this case decided to delete the entire folder. How does that happen? Well, it does! When it happens, the XE traces will start to fail and you will no longer capture the intended trace data. Let’s take a look at a simulated reproduction of this issue.
First, I will create a session and then start the session, then validate the session exists and then stop the session.
USE master;
GO
-- Create the Event Session
IF EXISTS ( SELECT *
FROM sys.server_event_sessions
WHERE name = 'SVRLoginAudit' )
DROP EVENT SESSION SVRLoginAudit
ON SERVER;
GO
EXECUTE xp_create_subdir 'C:\Database\XEDROPME';
GO
CREATE EVENT SESSION SVRLoginAudit ON SERVER
ADD EVENT sqlserver.login ( SET collect_database_name = ( 1 ), collect_options_text = (1)
ACTION ( sqlserver.sql_text,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.client_hostname,
package0.collect_system_time,package0.event_sequence,
sqlserver.database_id, sqlserver.database_name,
sqlserver.username,sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.session_id,
sqlserver.context_info, sqlserver.client_connection_id )
)
ADD TARGET package0.event_file ( SET filename = N'C:\Database\XEDROPME\SVRLoginAudit.xel' , max_file_size = ( 5120 )
, max_rollover_files = ( 4 ) )
WITH (STARTUP_STATE = ON
,TRACK_CAUSALITY = ON);
/* start the session */ALTER EVENT SESSION SVRLoginAudit
ON SERVER
STATE = START;
GO
DECLARE @SessionName VARCHAR(128) = NULL --'BackupThroughput' --'sp_server_diagnostics session' --NULL for all
;
SELECT ISNULL(ses.name,xse.name) AS SessionName
, CASE
WHEN ISNULL(ses.name,'') = ''
THEN 'Private'
ELSE 'Public'
END AS SessionVisibility
, CASE
WHEN ISNULL(xse.name,'') = ''
THEN 'NO'
ELSE 'YES'
END AS SessionRunning
, CASE
WHEN ISNULL(xse.name,'') = ''
AND ISNULL(ses.name,'') = ''
THEN 'NO'
ELSE 'YES'
END AS IsDeployed
FROM sys.server_event_sessions ses
FULL OUTER JOIN sys.dm_xe_sessions xse
ON xse.name =ses.name
WHERE COALESCE(@SessionName, ses.name, xse.name) = ISNULL(ses.name, xse.name)
ORDER BY ses.event_session_id;
GO
/* stop the session */ALTER EVENT SESSION SVRLoginAudit
ON SERVER
STATE = STOP;
GOAnd here is what I see on my test server when I validate the session exists.
Perfect so far. Now, let’s make that trace output directory disappear. For this demo, you might note that I had created the directory as “C:\Database\XEDROPME”. My intent in the name was obviously to notify the world that the folder was to be dropped.
xp_cmdshell 'rd C:\Database\XEDROPME /Q /s' GO
That statement is easy enough and is performed from my test environment for those getting weary of the use of xp_cmdshell. Now, let’s try to start that session that we knew was previously running.
I re-formatted the output of the error for ease of readability. Otherwise, the output in the preceding image is what will happen when the output directory is dropped. The fix is rather simple at this point – put the folder structure back into place. To read an introductory post about checking if a session exists or not on your server, check this out (a more advanced post is coming soon).
Conclusion
From time to time we will run into various problems supporting Extended Events. This is bound to happen more frequently as we support more varied environments with more hands in the kitchen (so to speak). We need to learn that even small changes can have a ripple effect to other things that may be running on the server. It is worthwhile to perform a little due diligence and clean things up as we make changes – or at minimum to observe the system for a time to ensure no unintended consequences have occurred.
Extended Events is a powerful tool to help in troubleshooting and tuning your environment. I recommend investing a little time in reading the 60 day series about Extended Events. This is not a short series but is designed to provide an array of topics to help learn the tool over time.
