none
SQL Server 2012: Error handling for multiple errors RRS feed

  • Question

  • Hi.

    Looking on the internet I'm pretty sure I know the answer to this question (i.e. "No") but I wanted to confirm my suspicions.

    I have a stored procedure which steps through a list of transaction log backups and applies them to a DR database with NORECOVERY. As part of our DR test process, it is sometimes possible that the DR database gets ahead of the transaction log list. In this circumstance I get the error:

    Msg 4326, Level 16, State 1, Line 8
    The log in this backup set terminates at LSN 74000000023300001, which is too early to apply to the database. A more recent log backup that includes LSN 74000000025200001 can be restored.
    Msg 3013, Level 16, State 1, Line 8
    RESTORE LOG is terminating abnormally.
    

    This is fine and I'm happy to ignore this log and move on to the next one. However there appears to be no way to catch the 4326 error. Wrapping the restore in a TRY ... CATCH only identifies the 3013 error number and that could be caused by many issues that I don't want to ignore. If my TRY ... CATCH uses the THROW command, both the 4326 and 3013 errors are displayed so my session clearly has a handle to them both.

    My questions are:

    • Can I catch the first error thrown?
    • Can I review all the errors thrown?
    • Can the output from THROW be captured in a variable so I can parse it?

    Thanks.

    Thursday, November 5, 2015 9:49 AM

Answers

All replies

  • Hello,

    If you can catch an error depends on the error servity, not the error message; see TRY...CATCH (Transact-SQL) =>
    Remarks => "A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection."

    You can get the last error message with the ERROR_MESSAGE (Transact-SQL)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, November 5, 2015 10:34 AM
  • Hi Olaf.

    Thanks for that but 4326 has a severity of 16 so, from the extract you've providing, should be hitting the CATCH. However the error handler is only reporting the 3013 which follows it.

    It would be interesting to know why the first error doesn't result in the CATCH block being called since it has an equivalent severity level.

    Peter

    Thursday, November 5, 2015 11:12 AM
  • Hi @ib483

    While using RAISERROR it will only catch the last error thrown, it won't catch and return all the errors.

    The new THROW keyword introduced in SQL 2012 returns all the errors, check this link: http://sqlwithmanoj.com/2015/02/04/capture-multiple-errors-in-try-catch-by-using-throw-statement/

    But I don't think if there is any mechanism to store both the errors returned by any script as after THROW the execution ends and control is transferred to the client.

    Other than using DBCC OUTBUFFER(@@spid), you will need to parse the multiple error details spread through several rows.


    ~manoj | email: http://scr.im/m22g
    http://SQLwithManoj.com
    MCCA 2011 | My FB Page


    Thursday, November 5, 2015 12:54 PM
  • Thanks Manoj.

    I'm not trying to do anything *after* the THROW. The pseudo code is:

        - Loop through a number of transaction log backups
            - Restore first backup
            - If failure, check if the error was 4326
                - If yes, ignore
                - If no, THROW
        - Go to next file

    Unfortunately when reaching the "If failure, check if error was 4326" step, the error code reports as 3013. The cause of *this* error is any sort of failure which may or may not be appropriate to ignore.

    Peter

    Friday, November 6, 2015 9:46 AM
  • Useful article on parsing DBCC OUTPUTBUFFER which proved useful in solving this problem for me:

    http://pjondevelopment.50webs.com/articles/retrieveErrorMessage.htm

    Friday, May 27, 2016 3:44 PM
  • An alternative is to use the CMD Catch Handler which I wrote for my article on Error and Transaction Handling in SQL Server. This is a CLR routine which runs the command on the context connection and traps the error.
    http://www.sommarskog.se/error_handling/Part3.html#CMDcatchhandler


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, May 27, 2016 10:01 PM