Should you always log database errors? Even if you think it’s redundant because the applications are already logging everything, having a trustworthy database error table is crucial for efficient troubleshooting sessions.

It requires some planning ahead, or rework if you already have an operational database, but in the long run it saves everyone’s time and money. Keep in mind I’m not saying that you should handle the errors (as I believe applications are better suited for this), but always log them.

Basic logging objects

First, you need a table to store the errors. Adding attributes to store the values from all the ERROR_ functions should be enough, but you can always extend the table as needed in the future.

CREATE TABLE meta.tDatabaseErrors
(
    IDDatabaseError int NOT NULL IDENTITY(1, 1) CONSTRAINT PK_meta_tDatabaseErrors PRIMARY KEY CLUSTERED
  , ErrorNumber int NOT NULL
  , ErrorMessage nvarchar(4000) NOT NULL
  , ErrorProcedure nvarchar(256) NULL
  , ProcedureParameters nvarchar(MAX) NULL
  , ErrorLine int NOT NULL
  , ErrorState int NOT NULL
  , ErrorSeverity int NOT NULL
  , ErrorDate datetime2(7) NOT NULL CONSTRAINT DF_meta_tDatabaseErrors_ErrorDate DEFAULT SYSDATETIME()
  , ErrorUser sysname NOT NULL CONSTRAINT DF_meta_tDatabaseErrors_ErrorUser DEFAULT SUSER_NAME()
);

Next, you should create a stored procedure that populates the table.

To clarify, the procedure by itself will log the error and “eat” it, if you don’t re-throw the error the parent process will most likely think the operation was successful.

CREATE PROCEDURE meta.csp_LogDatabaseError
(
    @ErrorMessage nvarchar(4000) = NULL
  , @ErrorProcedure nvarchar(256) = NULL
  , @ProcedureParameters nvarchar(MAX) = NULL
  , @ErrorUser sysname = NULL
)
AS
BEGIN

    SET NOCOUNT ON;

    SELECT
        @ErrorMessage = ISNULL(@ErrorMessage, ERROR_MESSAGE())
      , @ErrorProcedure = ISNULL(@ErrorProcedure, ERROR_PROCEDURE())
      , @ErrorUser = ISNULL(@ErrorUser, SUSER_NAME());

    INSERT INTO meta.tDatabaseErrors
    (
        ErrorNumber
      , ErrorMessage
      , ErrorProcedure
      , ProcedureParameters
      , ErrorLine
      , ErrorState
      , ErrorSeverity
      , ErrorDate
      , ErrorUser
    )
    VALUES
    (   ERROR_NUMBER()       -- ErrorNumber - int
      , @ErrorMessage        -- ErrorMessage - nvarchar(4000)
      , @ErrorProcedure      -- ErrorProcedure - nvarchar(256)
      , @ProcedureParameters -- ProcedureParameters - nvarchar(max)
      , ERROR_LINE()         -- ErrorLine - int
      , ERROR_STATE()        -- ErrorState - int
      , ERROR_SEVERITY()     -- ErrorSeverity - int
      , DEFAULT              -- ErrorDate - datetime
      , @ErrorUser           -- ErrorUser - sysname
        );
END;

Using the logging objects

Finally, the last piece of this logging structure is having a proper stored procedure template and using it to create ALL of your procedures, no matter how simple they are.

Even if there is no way for your procedure to fail, an unexpected database event could happen and you will be grateful to have a log of that. Alternatively, you might prefer explaining to your client that you have no idea how the procedure failed and no quick way to figure it out.

CREATE PROCEDURE dbo.csp_StoredProcedureTemplate
(
    @Param1 int = NULL
  , @Param2 nvarchar(10) = NULL
)
AS
BEGIN

    SET NOCOUNT ON;
    SET XACT_ABORT ON; --always use this if you're starting a transaction

    DECLARE
        @CustomErrorMessage nvarchar(4000) = NULL
      , @ProcedureParameters nvarchar(MAX) = NULL;

    BEGIN TRY

        SET @ProcedureParameters =
        (   --list your parameters here or remove if no parameters
            SELECT @Param1 AS Param1, @Param2 AS Param2 FOR JSON PATH
        );

    --start the logic here, don't forget to handle your transactions

    END TRY
    BEGIN CATCH

        IF (@@TRANCOUNT > 0)
        BEGIN
            ROLLBACK;
        END;

        EXEC meta.csp_LogDatabaseError
            @ErrorMessage = @CustomErrorMessage
          , @ErrorProcedure = NULL  -- set preferred name if using dynamic SQL
          , @ProcedureParameters = @ProcedureParameters
          , @ErrorUser = NULL;  -- can be used to override DB user with application user

        THROW; -- re-throwing the error

    END CATCH;

END;
GO

Summing up, only thing left for you is to get into the habit of using the above template and, to make it robust, start using versioning for your database project and implement code review.