CODEWITHSUNDEEP
sql-servert-sqldebugging
dspn
dspn

Reputation: 314

Creating a debug table

I have a couple stored procedures that write and delete data from some tables.

My idea is that those stored procedures would either write to the production table or to a debug table with what went wrong.

Is this a good idea? Any recommendations on how to do?

Here's what I have:

CREATE TABLE dbo.ErrorLog 
(
    [ErrorID]        int         IDENTITY(1,1) PRIMARY KEY,
    [SQLUser]        varchar(50) NOT NULL DEFAULT ORIGINAL_LOGIN(),
    [ErrorDate]      datetime    NOT NULL DEFAULT GETDATE(),
    [How]            varchar(50) NOT NULL, --The name of the stored procedure that was running
    [ErrorMessage]   varchar(50) NOT NULL,
    [ErrorProcedure] varchar(50) NOT NULL,
    [ErrorLine]      varchar(50) NOT NULL,
    [ErrorState]     varchar(50) NOT NULL,
    [ErrorSeverity]  varchar(50) NOT NULL,
    [ErrorNumber]    varchar(50) NOT NULL
)

And then the stored procedure is something like this:

CREATE PROCEDURE [dbo].[proc_xxx]
    @A int,
    @B int,
    @C int,
    @D int
AS
BEGIN
    DECLARE @BusinessRule_1_Fail int =
        (SELECT IIF(@A < 3, 1, 0))--some business rule that might result in 1 or 0
    DECLARE @BusinessRule_2_Fail int =
        (SELECT IIF(@C < 7, 1, 0))--some business rule that might result in 1 or 0

    IF @BusinessRule_1_Fail = 1
        RAISERROR('Rule #1 failed because XYZ', 16, 16) 
    ELSE IF @BusinessRule_2_Fail = 1
        RAISERROR('Rule #2 failed because ZYX', 16, 16) 

    BEGIN TRY
        BEGIN TRANSACTION
            INSERT INTO [INV].[Table_A] (ColA, ColB, ColC, ColD)
            VALUES (@A, @B, @C, @D)

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION

        INSERT INTO [dbo].[ErrorLog] ([SQLUser], [ErrorDate], [How], [ErrorMessage], [ErrorProcedure], [ErrorLine], [ErrorState], [ErrorSeverity], [ErrorNumber])
        VALUES (ORIGINAL_LOGIN()   ,  -- [SQLUser], 
                GETDATE()          ,  -- [ErrorDate], 
                '[dbo].[proc_xxx]' ,  -- [How], 
                ERROR_MESSAGE()    ,  -- [ErrorMessage], 
                ERROR_PROCEDURE()  ,  -- [ErrorProcedure], 
                ERROR_LINE()       ,  -- [ErrorLine], 
                ERROR_STATE()      ,  -- [ErrorState], 
                ERROR_SEVERITY()   ,  -- [ErrorSeverity], 
                ERROR_NUMBER()     ,  -- [ErrorNumber]
            )
    END CATCH
END

Is this a good idea? Bad? Anything you would do differently?

I was wondering to include the function parameters as well (to be able to reproduce the error) but given that each stored procedure has different input parameters it gets quite tricky (maybe having a varchar(8000) column that is calculated as something like 'A;10,B;2,C;323,D;42' for this stored procedure and as 'AA;1,BA;202,par8;3' for another stored procedure)

I wonder what the best practices for something like this are...

Upvotes: 1

Views: 223

Answers (1)

trenton-ftw
trenton-ftw

Reputation: 972

This is common practice in large data warehouses that are database first design. Typically what I see is that these solutions grow in usage and purpose over time, and making them with a model that is adaptable and generic helps in bullet proofing them them for the future. And really that is what makes these a success (that really goes for any logging framework as well).

Its probably worth noting that if you are writing this as the backend to an application, there are many robust logging frameworks that are going to be much more suited to this type of usage. In place of catching the error information in the stored procedure, you could allow an application to catch that information and then include it in your application logs.

Either way, here are a couple of pointers specific to your implementation example above:

  • Make sure the datatypes of your table match the datatype of the output functions that you intend to store there. For example, ERROR_MESSAGE() returns nvarchar(4000), but you are storing it in a column of varchar(50).

  • This type of logging is usually done by a stored procedure that accepts some context around the error and current circumstances (instead of doing an INSERT in line like you are currently doing). That helps with two main things:

    • Should you edit the way that your errors or messaging is handled in the future, you can hopefully just update the stored procedure in place of going in to each stored procedure you wrote and updating there.
    • Removes the repetitive calls for context to a stored procedure. For example, you don't need to call GETDATE() in your stored procedure where the error occurred. That can be done in a stored procedure that handles logging all of your errors and then accepts only the info it does not already have access to around the context of the error.

  • You should not call the table ErrorLog. I would call it something along the lines of just SqlMsg or something more generic and rename your other columns to not be named specific to errors. Add a column that indicates the type of message (Error being one of them). Then you can input more than just errors there. For example, I have a similar set up where our stored procedures accepts a parameter for a logging level (similar to .NET logging configuration) and can log performance or info messages as well. That way we only log that information (with the added overhead) when required and are still able to locate errors from this table. This allows more advanced troubleshooting if required, and also allows simple performance measurements and counters to come from the same solution.

  • You should include a column of xml or json datatype with the name of MoreDetail or something like that. What this does is gives you a place to do advanced discrete logging that can still be queried later on. Since the discrete values that you care about might differ per stored procedure, this will allow you to log differing information in this table without the need to change the actual schema of the table itself.

  • You should include a column that includes the all parameter values of the stored procedure as static values. For example, if the stored procedure dbo.Test has the variable @Input and is called with EXEC dbo.Test @Input = @SomeOtherVariable; what you want to know in your log entry is @input = 'SomeOtherVariableValue'. This makes it easier to reproduce errors (as you mentioned) and allows debugging linked stored procedure calls much easier.

Upvotes: 2

Related Questions