Post reply

Store error log entry that survives transaction rollback?

  • September 27, 2012 at 12:43 pm

    #282132

    I would like to find a way to write to an error log when an error occurs, and have that error survive a transaction rollback.

    Initially, I thought that sending a message to a Service Broker queue could help, but I now understand that the SEND will be rolled back too.

    (It would be really nice if BEGIN DIALOG or SEND ON CONVERSATION would accept a flag to indicate that changes to the queue should be outside the current transaction...)

    What are some other strategies to initiate a log entry from inside TSQL code that will survive a transaction rollback? I suppose a loopback connection from an extended stored procedure might be one way. Is there a cleaner and safer way to persist error log entries after a transaction rollback?

  • September 27, 2012 at 12:58 pm

    #1543061

    Insert the data into a table variable, after the rollback insert that into a real table or whatever else you want to do with it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 27, 2012 at 1:14 pm

    #1543069

    Thanks for the suggestion about a table variable. I know that operations on table variables don't get rolled back. Maybe I can find a way to make use of this fact in my scenario.

    I have nested stored procedures such as A calls B, B calls C. When I encounter an error in C, ideally I could directly log that error in detail from C, but let the exception bubble up to A where the rollback would happen.

    Perhaps if C logged the error as normal, but then in the error handler of A the errors were read into a table variable, then the transaction rolled back, then the errors re-written to the error log... this might work for me.

  • September 27, 2012 at 1:17 pm

    #1543072

    Worth trying, just watch the locking on your logging table, don't want exclusive locks held too long.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply