I am collecting errors data using the error_reported event and saving the data on XEL files. The problem is that there are hundreds of thousands of errors over there and querying them can take a lot of time.
One option is to prevent errors from being logged twice if they happened on the same object in the same timestamp. I really don't think the second option is possible.
Second option is to index the file in a way that it will be quick to filter errors by the error text and the object that caused them.
So far I thought of creating a separate process that will run in the background and writes filtered values from the files to an indexed table, but its a bad solution. That process would need maintenance and could be expensive on IO resources.
So far I didn't find a way to solve it on the extended event level.
Does anyone have a better idea?
Best Answer
You could potentially filter the events using a custom program, but that would require a fair amount of work (mostly in testing) to ensure that you don't adversely impact the SQL Server.
You're probably best off simply logging all the errors, and sorting through them later (possibly on a different computer). You can read the XEvent files with the function sys.fn_xe_file_target_read_file .