SQL Server error logging from a Stored Procedure

Question

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.

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

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

Is this a recommended approach to log SQL Server errors?

FWIW I use SQL Server 2008

Answer 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

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:

1. 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).
2. 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.

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

Answer 2

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

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.

Answer 3

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.

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.