Capturing bespoke SQL Errors
matt_rushton
Posts: 5 New member
I note that SQL Mon can enable Traces to capture events on either side of an alert.
I also note that by default SQL Mon alerts on any SQL Error that hits the log which is severity > 20.
How do I go about setting up a bespoke alert on a specific SQL error number that is severity 16 so that SQL Mon both alerts that the error happened and I can then view the trace on either side of the error?
Specifically, I want to temporarily alert on error 3960 on one specific database in my estate to troubleshoot an application error with Snapshot isolation transactions.
I also note that by default SQL Mon alerts on any SQL Error that hits the log which is severity > 20.
How do I go about setting up a bespoke alert on a specific SQL error number that is severity 16 so that SQL Mon both alerts that the error happened and I can then view the trace on either side of the error?
Specifically, I want to temporarily alert on error 3960 on one specific database in my estate to troubleshoot an application error with Snapshot isolation transactions.
Tagged:
Answers
I'm not sure this will be possible in the specific way you are looking for.
The trace is enabled at the instance level in Configuration > Trace, so it would be on for all alerts that occur on that instance and only a specific set of trace information is captured (see What trace data is captured on https://documentation.red-gate.com/sm/adding-and-managing-monitored-servers/managing-monitored-servers/configuring-profiler-trace). So you will need to go to the page above and enable trace for the instance in question.
For the SQL Server Error log Entry we capture events into SQL Monitor for anything above the severity it is configured to alert at, so you would need to set the threshold severity to 16 to be able to capture the event. This alert is also customizable down to the SQL Server instance level, so you would go to Configuration > Alert settings and then click on the SQL Server Error Log Entry alert and then drill down to the instance level in question in the hierarchy tree at the left and then customize the settings for that instance. This may cause more alerts to be raised since there will potentially be more error log entries with severity 16-19 that will cause an alert to be raised.
These will both continue until you disable trace and modify the alert settings back to inherit or whatever other level you would choose.
If you mean about creating a custom metric to do this, you would need to be able to write a script that would read the error log and then return a value based on that being seen or not, which you would need to set the alert to fire on. The trace data shown on the alert would be the same as above and enabled in the same way.
I hope that helps and if I've misunderstood please do let me know.
Kind regards,
Alex
Have you visited our Help Center?
I can see how that would work but I don't want to change the global severity filter down to 16 because as you say, it would over capture and alert on too many errors.
More specifically, I'd like instruction as to how to set up a custom metric that uses Extended Events to only capture the error number in question.
By using Extended Events I could turn the XE off when I'm finished with my investigation without touching SQL Mon.
All the Redgate tutorials I've found seem focused on capturing performance metrics and not errors and I'm an old Trace guy with next to no experience using XEs yet so I'm coming at this new to both Extended Events and SQL Mon.
The custom metrics are only able to keep track of a single scalar value being returned, so you would need to read the event file (this may help https://www.brentozar.com/archive/2015/01/query-extended-events-target-xml/) and return e.g. a 0 or a 1 depending on whether the error is found or not to SQL Monitor and alert based on that.
Kind regards,
Alex
Have you visited our Help Center?