How to implement logging and error reporting in SQL stored procedures?

Question

Background

I'm currently working on a project that heavily utilizes SQL stored procedures. Some tables have as many as few hundred thousand procedure executions every day. Because the project serves critical business function for our client logging and fast error detection is crucial.

Question

How to implement logging and error reporting in SQL stored procedures that is fast and reliable at the same time?

Answer 1

IMO one of the fastest ways you can log is by writing to a table. My old DBA used to set up a "Utilities" database on a different hard drive for this purpose, but with the virtual machines, I don't that doesn't matter anymore.

As far as possible, go with an INSERT-ONLY data model for your logs (new row inserted each time) as opposed to an INSERT-UPDATE model (insert row, update status periodically)

For error trapping for new procedures or when you re-write old ones, you can use TRY-CATCH

http://msdn.microsoft.com/en-us/library/ms175976.aspx

Answer 2

Here is a very complete article about Error Handling in Sql Server (for version 2000 though). For the new procedures, as Raj More said, just use try-catch.

Answer 3

What we have done is: set up a table variable at the start of the proc (it's important that it be a table variable not a temp table) with the fields for the information you want to log. Actually I often use two, one for the actual error and one for the steps that have completed successfully if it is a long multi-step proc.

1. Use try catch
2. Start a transaction
3. Before each step, log the step starting into one of the table variables.
4. When an error occurs and you hit the catch block, put the error message into a a table varaible for the eroors and rollback the transaction.
5. The table variables are still available after the rollback, so then you insert that data into the the logging tables.

Answer 4

This won't help you at the moment but may be of interest to people on SQL Server 2008. On SQL Server 2008 XEvents can be used to log all error details (including statement text) to a central location.

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='ErrorLogger')
    DROP EVENT SESSION [ErrorLogger] ON SERVER;
CREATE EVENT SESSION [ErrorLogger]
ON SERVER
ADD EVENT sqlserver.error_reported(
     ACTION (sqlserver.sql_text)
     WHERE (([severity]>(10))))
ADD TARGET package0.asynchronous_file_target(
     SET filename='c:\temp\error_logger.xel', metadatafile='c:\temp\error_logger.xem')
WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, 
MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0KB, 
MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = ON)

ALTER EVENT SESSION [ErrorLogger] ON SERVER STATE = START

And to review the errors

SELECT CONVERT (XML, event_data) AS data
        FROM sys.fn_xe_file_target_read_file ('C:\Temp\error_logger*.xel', 'C:\Temp\error_logger*.xem', NULL, NULL)