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?


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:StatementCompleted

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.

  • Looks pretty cool. Do Extended Events require CLR integration be enabled for the instance? This is a 32-bit server, and we've run into some nasty VAS problems before when we try to enable it. – db2 Jan 11 '12 at 20:44
  • No, they don't require that. There is a default system_health extended events trace running out of the box. Good overview of them here – Martin Smith Jan 11 '12 at 20:46
  • Good to know. I've got a trace running for now, but maybe I'll experiment with that when I get a chance. – db2 Jan 11 '12 at 21:02

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.