Batch aborting errors

Some errors are so serious that they can't be handled like normal errors. Example:

begin try
   -- Do something (the task you want to perform)
end try
begin catch
   -- Do something (log error, rollback transaction etc)
end catch

You might think that any error will be caught in the catch-clause allowing you to log the error and rollback any open transaction. This is WRONG!!! Some errors are batch aborting, this means that immediatly when they are encountered they stop the processing, rollback any transaction and close the connection. An example of a batch aborting error is to access an object that doesn't exist.

This shouldn't be an issue since SQL Server will do necessary cleaning, only problem is that your fancy code for logging the error will not be executed.

I've seen one example when SQL Server for some reason doesn't manage to abort the transaction, this was a big problem. Taking a backup and restore it would solve this problem, strange... no errors where reported by DBCC CHECKDB but the restore helped.

More information on:
http://www.sommarskog.se/error-handling-I.html