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?

  • So you can't just filter in the event session because the there are just lots of occurrences of each error? – David Browne - Microsoft Oct 28 '18 at 17:55
  • Yes, many of the errors are caught, thus there are many errors that repeat themselves inside a loop. The log is just being bombarded with data – Yuval Perelman Oct 28 '18 at 19:53
  • Roughly what is the rate of error generation? Hundreds of thousands of errors over what period of time? – David Browne - Microsoft Oct 29 '18 at 13:03
  • it can be a few minutes, but its very general. Its a large system with a lot of exceptions thrown from many places all the time, I'm trying to sort it somehow – Yuval Perelman Oct 29 '18 at 14:10

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 .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.