如何在 SQL 存储过程中实现日志记录和错误报告?

2021-12-20 00:00:00 logging sql sql-server-2005 sql-server stored-procedures

背景

我目前正在从事一个大量使用 SQL 存储过程的项目.有些表每天执行多达几十万个过程.因为该项目为我们的客户日志服务提供关键业务功能，快速错误检测至关重要.

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.

问题

如何在 SQL 存储过程中实现既快速又可靠的日志记录和错误报告?

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

推荐答案

目前这对您没有帮助，但 SQL Server 2008 上的人可能会感兴趣.在 SQL Server 2008 上 XEvents 可用于记录所有错误详细信息(包括声明文本)到一个中心位置.

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

并检查错误

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)

相关文章