Tuesday, August 17, 2021

What TRY/CATCH Doesn’t Handle In SQL Server

 It’s well documented in Books Online (BOL). If you’re like me, then 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 them short. Do your selects, variable settings, background info gathering, etc before the transaction starts.


Article Courtesy : Brent Ozar

No comments:

Popular Posts