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?
Sql-server – Log all user error messages for retrospective troubleshooting
sql serversql-server-2008
Related Question
- Sql-server – SQl Server 2008 R2 – sa can login but not access database
- Sql-server – Best option to index an application log table for quick insertion and retrieval in date order
- Sql-server – Database in “Restoring” state
- Sql-server – SQL Server detected a logical consistency-based I/O error – tempdb
- SQL Server 2019 – Fix InitPersistentVersionStore Failed After CU2 Installation
Best Answer
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
SQL:StatementCompletedAs 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.