How to automatically create a log when an exception is being received into sql server?
Answers
Answer:
In many environments there is a lack of consistent logging from stored procedures - if there is any logging at all. In general, we tend to leave it up to the application to record any errors, but in systems with many different applications, it can be tedious to collect exception information and figure out which stored procedures are having issues. And more generically, it is difficult to determine which stored procedures are being used at all, without running a server-side trace for a significant amount of time.
Solution
By creating a central logging system, you can add both error and general usage logging to stored procedures with minimal intrusion. On my systems I create a separate database called Utility which contains general purpose and system-wide items like this. So if you don't have a generic database for this type of purpose, you can create one:
CREATE DATABASE Utility;
GO
Now, what kind of things would you be interested in logging from your stored procedures? For a starting list:
Date/time
DatabaseID
ObjectID
Fully-qualified procedure name
Line number
Error message
Any additional info you want to pass