如何在SQL存储过程中实现日志记录和错误报告? [英] How to implement logging and error reporting in SQL 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:\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

并检查错误

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)

这篇关于如何在SQL存储过程中实现日志记录和错误报告?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！