T-Sql Error Handling and Logging

Question

Hi

I am trying to maximize the benefits from an experience.

Also I usually use Enterprise library logging block, I log errors and a portion of statistical information into the database, because it is centralized place to track errors, if database logging failed, Normally it goes to Event Log.

Tracing messages should go into file:

Which choice you believe we should go

1- Only Some tracing messages can be left in code if there is a complex algorithm or unstable module.

OR

2- We should not keep any tracing messages in code, clean it up as soon as bug is resolved.

For database.

I think that Errors raised from SP and functions should be logged into another table in the database, and that exactly what is done by AdventureWorksLT2008 database.

Is it a bad idea to log database events directly to Enterprise library Log table without raising this errors to next tier. I think it is more fixable, because I can put more custom information in the message. of course some errors will not be handled and will reach the next tier.

Any ideas, or comments, something else you do. something you want to clarify.

Thanks

Answer 1

What if your database has performance issues and SP/functions start timing out - logging the error to the database may not work?

Answer 2

Are you talking about catching errors and logging directly in T-SQL and not then doing RAISERROR to get it to the caller?

I think that's a viable strategy for certain kinds of issues - for instance, if an SP wants to find a problem and correct it silently and simply issue a warning.

But the kind of issues it would apply to might not be terribly frequent.

The kind of things I would think about are things like unusual cases where unexpected UPDATEs are done instead of INSERTs? Or where data already exists so is not generated. Or in a deployment or build script which skips an existing table, etc.