sql server - 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

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:emperror_logger.xel', metadatafile='c:emperror_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:Temperror_logger*.xel', 'C:Temperror_logger*.xem', NULL, NULL)