How can I handle lost client connection in a SQL Server T-SQL try-catch block?

Question

The TSQL BEGIN TRY and BEGIN CATCH block pattern does not catch errors due to a lost client connection. How can I catch and handle a lost client connection?

I'm setting a flag that indicates processing while processing a loop of individual transactions and the catch block re-sets that flag on error, but if the client connection is lost, my catch block does not execute...

PSUEDOCODE:

BEGIN TRY
  SET FlagToIndicateProcessing = 1
  LOOP START
    BEGIN TRANS
      DO WORK
    COMMIT TRANS
  LOOP END
  SetSomeValues
  SET FlagToIndicateProcessing = 0
END TRY
BEGIN CATCH
  SetSomeValues
  SET FlagToIndicateProcessing = 0
END CATCH

Answer 1

A loss in database connectivity falls out of the scope of what TRY/CATCH in T-SQL can handle.

You have a number of options, but that depends on what's acceptable (from a design standpoint) in your circumstance. Not being privy to that info, it's hard to make suggestions.

Having said that, one idea would be to wrap everything in a "parent" transaction. If that transaction is rolled back (which happens by design if you lose connectivity), then no changes in that session are persisted to the database.

Answer 2

Correct.

A client lost connection is effectively an abort and rollback to SQL Server: you simply stop executing SQL so the CATCH block will not execute.

Why are you batching individual transactions in a loop? This may help someone suggest a solution. Often, one SQL call is one unit of work.