SQL SERVER 2005: Use XACT ABORT to roll back non trapable error transactions

The Usual way to code for transaction is
BEGIN TRANSACTION;
BEGIN TRY
--What if you are calling a function which is missing in the DB
--What if you are reference a table in another database without synonym
--Under some of these above issues the transaction will not roll back
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SELECT @Return = ERROR_NUMBER()
SELECT ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_LINE () as ErrorLine,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_MESSAGE() as ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;

RETURN @Return
END CATCH

So in order to catch non trapable error transactions use SET XACT_ABORT ON it traps all the errors and rolls back the Transaction

Another good link to check http://wiki.lessthandot.com/index.php/Use_XACT_ABORT_to_roll_back_non_trapable_error_transactions