What TRY/CATCH Doesn’t Handle

We were once asked in class what TRY/CATCH doesn’t handle besides object existence errors.

It’s well documented in Books Online (BOL). If you’re like me, then tl;dr. Are we even calling it Books Online these days? I still say “bookmark lookup” instead of “key lookup”. I suppose I’ll be saying Books Online for quite some time too. At least these days it really is online.

Here’s a shortened version:

  • Warnings or informational messages that have a severity of 10 or lower
  • Errors that have a severity of 20 or higher that stop the session
  • Attentions
  • When a session is KILLed

In addition to the above, there are some errors that are not handled when it occurs at the same level as the TRY/CATCH:

  • Compilation errors
  • Errors that occur during statement-level recompilation (object existence errors fall into this category)

In all of these cases, be sure that the calling application/object handles the error.

My favorite usage of a TRY/CATCH construct is when you are purging data and retrying the DELETE due to a deadlock:

Brent says: when you’re looking at TRY/CATCH, it’s also good to remember the traditional guidance about transactions: keep ’em short. Do your selects, variable settings, background info gathering, etc before the transaction starts.

Previous Post
SQL Interview Question: “Talk me through this screenshot.”
Next Post
Extended Events: Where They Hide The Good Stuff

21 Comments. Leave new

  • Thank you, Tara.
    This is very important especially “Compilation errors” case.

    I’d like to add more comment.
    Typically, we can represent the case with 207 or 208 error#, Invalid column/object name.

    Here is one simple example.
    Below statement will be ended with transaction OPEN state.

    BEGIN TRAN
    BEGIN TRY
    SELECT * FROM NoExistTable
    COMMIT TRAN
    END TRY
    BEGIN CATCH
    IF @@TRANCOUNT>0
    ROLLBACK TRAN
    END CATCH

    GO
    SELECT @@TRANCOUNT

    It could happen more frequently in Dynamic SQL statement.
    We can resolve the issue with “SET XACT_ABORT ON”.

    Thanks,

    Reply
    • Hey Simon…

      What if XACT_STATE = -1 (error).

      You aren’t going to get any resolution if you’re only looking at the successful (1) transactions.

      I believe 0 means nothing happened.

      Reply
      • XACT_STATE definitely needs to check every transaction for error handling.
        The example I gave one is a simple case to explain compile error issue.

        When you test it, it’s not hit the “Catch” block at all.
        It just stops at the “SELECT * FROM NoExistTable”.
        So, any statements in Catch block aren’t executed even you check XACT_STATE.
        This case only the resolution is XACT_ABORT setting.

        I just want to explain the specific case of “Compilation errors” which is not able to handle by Catch block.

        Simon

        Reply
  • Compilation errors can’t be handle if they are in the same scope. If such “errors” come from a “subscope”
    EXEC(‘SELECT * FROM dbo.NonExistingTable’) then TRY/CATCH will intercept them:
    BEGIN TRY
    EXEC(‘SELECT * FROM dfagsfdh’)
    END TRY
    BEGIN CATCH
    SELECT ERROR_MESSAGE()
    END CATCH

    Reply
  • Davide Marzucco
    May 24, 2016 11:08 am

    We found a tricky limitation in try/catch.
    It seems that in the catch block you can refer to the ERROR_XXXXX() functions in order to get details about the latest exception occurred only. I mean, If I’m getting two errors with a single statement, in the catch block I will always get information of the latest error only.

    For example, if you set a database to single_user and then try to do a backup of it, you will get two errors, one saying that the db is in single user, and one saying the backup is terminating abnormally.

    Wrapping the code in a try/catch will result in getting only the error of backup terminating abnormally. No way to get the real reason for that, that is db in single user.
    Ok you can use throw statement to get all the exceptions, but this will release the control in the code, losing any advantage of the catch routine.

    What do you think about it?

    Reply
    • Dumb question – why would you put a database in single_user mode to do a backup?

      Reply
      • Davide Marzucco
        May 24, 2016 11:12 am

        It was related to a migration/scale out process, when we need to move a database from an instance to another. Setting single_user will make sure no further data change will be made to the db. The db would have been dropped after the backup, and then restored on another instance.

        Reply
      • Stephane St-Louis
        May 31, 2019 12:51 pm

        That was a legitimate question. Regardless of the reason _why_ the database would be in single_user mode, it illustrates a real problem where a relevant reason for failure is being hidden. I actually see Davide’s example as a very good way to simulate the issue, in the same manner as I sometimes deliberately put a “1/0” in a VIEW to provoke an error and test my error handling code.

        Just like Davide, I ran into a similar issue where I was performing a backup on a server where the backup path had been altered. The backup operation was failing because of an underlying NTFS permission issue. But the calling procedure’s TRY/CATCH block which was responsible for logging errors would only record “BACKUP DATABASE is terminating abnormally” as an ERROR_MESSAGE(), which didn’t provide much insight into the source of the problem.

        The concealed underlying error message, the only one that really mattered, was the following:

        Cannot open backup device ‘(filepath\name)’. Operating system error 5(Access is denied.).

        That is the error message that would have helped me quickly understand the nature of the problem. I could enumerate dozens of random circumstances which could cause a backup to fail… none of which will be visible in the CATCH block, meaning none will be properly logged, nor properly handled.

        Relevant backup error messages are being concealed when using ERROR_MESSAGE() in a TRY/CATCH block. This is a real problem, and I am still looking for a proper way around it. Until then, whenever I’ll run into a problem, I’ll need to repeat the backup command manually in SSMS and hope for the failing condition to be consistent enough for me to be able to reproduce the problem.

        Reply
  • Tara, thank you for this article

    There also look to be some catch with regards to using linked server queries in the TRY…CATCH construct
    For example, the exception below is not trappable inside the TRY…CATCH if a linked server is involved:
    Error: 213, Severity: 16, State: 7
    Column name or number of supplied values does not match table definition.

    If a linked server is not involved though, the same exception is perfectly trappable

    Below are the examples of the non-trappable “Column name or number of supplied values does not match table definition.” exceptions:
    (none of the four ways listed below traps the exception)

    DECLARE @exec_str nvarchar(max);
    BEGIN TRY

    /* option 1 */
    INSERT my_local_db.dbo.my_local_table( field_1, field_2, field_3)
    EXEC MY_LINKED_SERVER.the_db.dbo.the_proc ‘20140718’ ;

    /* option 2 */
    –INSERT my_local_db.dbo.my_local_table( field_1, field_2, field_3)
    — EXEC( ‘ EXEC MY_LINKED_SERVER.the_db.dbo.the_proc ”20140718” ‘);

    /* option 3 */
    –SET @exec_str = ‘
    –INSERT my_local_db.dbo.my_local_table( field_1, field_2, field_3)
    — EXEC MY_LINKED_SERVER.the_db.dbo.the_proc ”20140718” ;
    –‘;
    –EXEC sp_executesql @exec_str;

    /* option 4 */
    –SET @exec_str = ‘
    –INSERT my_local_db.dbo.my_local_table( field_1, field_2, field_3)
    — EXEC( ” EXEC MY_LINKED_SERVER.the_db.dbo.the_proc ””20140718”” ”);
    –‘;
    –EXEC sp_executesql @exec_str;

    END TRY
    BEGIN CATCH
    SELECT ‘CATCH’;
    SELECT ERROR_MESSAGE();
    END CATCH

    The reason this behavior has caught my attention originally, is that there looks to be something special about this particular exception: there are other “more severe” situations, like missing [the_proc] procedure altogether, that are still perfectly trappable in the TRY..CATCH

    Reply
  • yeah and connection issues, at least with linked severs, are also not handles by try/catch… Another catch! *drumroll*

    Reply
  • Hi Tara/Brent, Just wonder if the Try/Catch block gets to execute during an automatic failover of SQL AlwaysOn environment. One of our application has a loop to do insert/delete and after an automatic failover (AAG), the application hung. I thought the SQL that was executed during the failover may have interrupted and rolled back, but how to handle or capture this exception in an alwayson environment?
    Please shed some light to this.
    Thanks & regards,
    Irene

    Reply
    • It’ll get rolled back during crash recovery. You’d have to handle the exception from the application as the connection is now severed. If the application hung due to a failover, I believe the issue is in the application. Do a test in a test environment. Doesn’t even have to be an AG, just stop the service while the application is trying to run a query and then see what happens.

      Reply
  • Hi, I’m curious why you need to check XACT_STATE in the try block before you commit? If there has been an issue would it not have taken it into the catch being the try should always be safe to commit?

    Reply
    • Gavin, it’s explained in the examples section of the XACT_STATE doc: https://docs.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql?view=sql-server-2017. “The following example uses XACT_STATE in the CATCH block of a TRY…CATCH construct to determine whether to commit or roll back a transaction. Because SET XACT_ABORT is ON, the constraint violation error causes the transaction to enter an uncommittable state.” If there isn’t an active transaction, the rollback will throw an error.

      Reply
      • I can see why you’d check in the catch block but I can’t see why the try block. Is there a situation where a transaction can go into an uncommittable state but not trip into the catch block?

        Reply
        • Just coding safeguards. It’s in the template I use for try/catch transactions.

          Reply
          • I realise this is just a sample so probably not really a valid point but… By having that xact_state check in the try block if somehow something does put the transaction in an uncommittable state and not trigger the catch you’ll handle it in the try block by not running the commit and essentially return a success back to the calling code. I’d almost rather the try just ran commit triggering it to fall into the catch block if there is a problem where the error is then handled and probably passed on to the calling code. Potentially I’m missing something here though.

            Anyway thanks for the posts, I’ve learnt a lot from this blog.

          • Honestly I have no idea, but I get what you’re saying. I’m a production DBA. That code is just from a template that I grabbed somewhere a long time ago when try/catch was added to TSQL. I don’t usually write code anyway. lol

  • Can someone confirm the following setting has to be present in order to use XACT_STATE( )?

    SET XACT_ABORT ON;

    And if so, should it be set once globally? Or is it per Connection? In other words, should I include it as first line in my Stored Procedure?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}