从存储过程记录SQL Server错误

Our application is Windows Service (native .EXE written in C++) that calls stored procedures in SQL Server. In most cases errors in stored procedures (in 90% of the cases these errors mean something was wrong in our business logic) are re-thrown as exception and caught by our service. They are then logged in Application Event Log on the computer where our service is running.

我们的应用程序是Windows服务(用C ++编写的本机.EXE),它调用SQL Server中的存储过程。在大多数情况下,存储过程中的错误(在90%的情况下,这些错误意味着我们的业务逻辑中出现了错误)被重新抛出作为异常并被我们的服务捕获。然后,它们将在运行我们服务的计算机上的应用程序事件日志中登录。

However, I now have a need to log some of the errors on the SQL Server itself within a stored procedure.

但是,我现在需要在存储过程中记录SQL Server本身的一些错误。

Following the paradigm we use for our service I think I can use xp_logevent to save error information in the event log.

遵循我们用于服务的范例,我想我可以使用xp_logevent在事件日志中保存错误信息。

Is this a recommended approach to log SQL Server errors?

这是记录SQL Server错误的推荐方法吗?

FWIW I use SQL Server 2008

FWIW我使用的是SQL Server 2008

4 个解决方案

#1

The How To

如何

You can always use RAISEERROR() WITH LOG. Logs to both Windows Application log and the SQL error log.Please note that severity level is key here. There are some limitations and security considerations, but you get some other features also. More details in BOL: http://msdn.microsoft.com/en-us/library/ms178592.aspx

您始终可以使用RAISEERROR()WITH LOG。记录Windows应用程序日志和SQL错误日志。请注意,严重性级别在此处是关键。有一些限制和安全注意事项,但您也可以获得其他一些功能。 BOL中的更多细节:http://msdn.microsoft.com/en-us/library/ms178592.aspx

The Should you

你应该的

My opinion is that you shouldn't log anything to SQL error log unless it's generated by SQL server itself. Multiple reasons:

我的意见是你不应该将任何内容记录到SQL错误日志中,除非它是由SQL服务器本身生成的。原因有多种:

If your IT or DBA uses log analyzer or any other tool, it may trip an alarm on an application issue, instead of the server issue (this is what they are trying to catch).

如果您的IT或DBA使用日志分析器或任何其他工具,它可能会在应用程序问题上发出警报,而不是服务器问题(这是他们试图捕获的)。

I never found parsing error logs enjoyable from within SQL server, and I'm not particularly in love with SSMS's way of doing it.

我从来没有发现从SQL服务器中解析错误日志,我并不是特别喜欢SSMS的做法。

My suggestion

Use a generic logging stored procedure writing to some error log table. A very nice patter is

使用通用日志记录存储过程写入某些错误日志表。一个非常好的模式是

BEGIN TRY
...do your stuff
END TRY
BEGIN CATCH
  get the ERROR_LINE(), ERROR_MESSAGE() and friends
  execute generic logging procedure
END  CATCH

As a bonus, you can use SSSB within the logging procedure to make it async and not impede the main logic flow

作为奖励,您可以在日志记录过程中使用SSSB使其异步并且不会妨碍主逻辑流程

#2

Here is a useful way I have found to keep track of SQL Server errors. First, create a table to store the errors:

这是我发现跟踪SQL Server错误的有用方法。首先,创建一个表来存储错误:

CREATE TABLE utiliity.dbo.ProcedureLog
(
 LogDate     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 DatabaseID    INT,
 ObjectID    INT,
 ProcedureName      NVARCHAR(400),
 ErrorLine    INT,
 ErrorMessage       NVARCHAR(MAX),
 AdditionalInfo     NVARCHAR(MAX)
);
GO
CREATE CLUSTERED INDEX cx_LogDate ON dbo.utiliity.dbo.ProcedureLog(LogDate);
GO

Then create a stored procedure to call when the error occurs:

然后创建一个存储过程以在发生错误时调用:

CREATE PROCEDURE sp_CallProcedureLog
 @ObjectID       INT,
 @DatabaseID     INT = NULL,
 @AdditionalInfo NVARCHAR(MAX) = NULL
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE 
  @ProcedureName NVARCHAR(400);

 SELECT
  @DatabaseID = COALESCE(@DatabaseID, DB_ID()),
  @ProcedureName = COALESCE
  (
   QUOTENAME(DB_NAME(@DatabaseID)) + '.'
   + QUOTENAME(OBJECT_SCHEMA_NAME(@ObjectID, @DatabaseID)) 
   + '.' + QUOTENAME(OBJECT_NAME(@ObjectID, @DatabaseID)),
   ERROR_PROCEDURE()
  );

 INSERT utiliity.dbo.ProcedureLog
 (
  DatabaseID,
  ObjectID,
  ProcedureName,
  ErrorLine,
  ErrorMessage,
  AdditionalInfo
 )
 SELECT
  @DatabaseID,
  @ObjectID,
  @ProcedureName,
  ERROR_LINE(),
  ERROR_MESSAGE(),
  @AdditionalInfo;
END
GO

Finally, in your stored procedures where you want to record the errors:

最后,在您要记录错误的存储过程中:

BEGIN TRY
   ... execute SQL commands here
END TRY
BEGIN CATCH
    DECLARE @msg NVARCHAR(MAX);
    SET @msg = 'Something went horribly wrong.  Error number = ' + ERROR_NUMBER();
    EXEC utiliity.dbo.sp_CallProcedureLog 
    @ObjectID       = @@PROCID,
    @AdditionalInfo = @msg;

    DECLARE @ErrorMessage NVARCHAR(MAX);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
            );               
END CATCH

Here are my sources: http://www.mssqltips.com/sqlservertip/2003/simple-process-to-track-and-log-sql-server-stored-procedure-use/ and http://msdn.microsoft.com/en-us/library/ms178592(SQL.105).aspx. HTH.

以下是我的资料来源:http://www.mssqltips.com/sqlservertip/2003/simple-process-to-track-and-log-sql-server-stored-procedure-use/和http://msdn.microsoft。 COM / EN-US /库/ ms178592(SQL.105)的.aspx。 HTH。

#3

The downside is that whoever has to find out the errors now needs permissions to get into the event log.

缺点是,现在必须找出错误的人需要具有进入事件日志的权限。

If you go with this, make sure your log has more size than the default 512K. Also set it to overwrite events as needed.

如果你这样做,请确保你的日志大小超过默认的512K。同时将其设置为根据需要覆盖事件。

Also, the event log is not as fast as your SQL Server database so you may want to run a load test to figure out if it slows your application down.

此外,事件日志没有SQL Server数据库那么快,因此您可能希望运行负载测试以确定它是否会降低应用程序的速度。

#4

You can call xp_logevent to log messages in the event log. But for logging exceptions it is better to use the RAISERROR () WITH LOG statement.

您可以调用xp_logevent来记录事件日志中的消息。但是对于记录异常,最好使用RAISERROR()WITH LOG语句。

If you are concerned about performance you can pass the message through a SQL Server Service Broker queue and have an activation procedure log the messages in the eventlog.

如果您担心性能,可以通过SQL Server Service Broker队列传递消息,并使用激活过程在事件日志中记录消息。

#1