Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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