Suppose I want to log all severity 16 (and higher) user error messages to a database table in order to allow some retrospective application troubleshooting (since the answer to "Can you send me the error message?" is quite often "No, I closed it.") Is a server-side trace going to be the most effective option, or would there be an even better approach?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
The problem with a server side trace is that logging the error messages without any context probably isn't very helpful and collecting that context requires tracing and filtering the more expensive events such as
As you are on SQL Server 2008 you can look at extended events for this. This is much more efficient as instead of capturing all statements and then discarding those without error it only fires on error then retrieves the statement text. For some example code see my answer here. though I recommend you download the Extended Events SSMS Addin and customize it for your needs.