Log Shipping – NORECOVERY vs. STANDBY mode

SET XACT_ABORT specifies what action SQL Server should take following run-time errors. The default session setting is SET XACT_ABORT OFF, which indicates that only the Transact-SQL statement that raised the error is rolled back and the transaction continues. Depending on the severity of the error, the entire transaction and/or batch may be rolled back even with SET XACT_ABORT is OFF.

A side effect of SET XACT_ABORT OFF is that a cancel/timeout error can leave an open transaction so it’s the client’s responsibility to cleanup following cancel/timeout. To safeguard against leaving an open transaction, applications that execute transactions with SET XACT_ABORT OFF need to roll back transactions and perhaps close the connection following SQL exceptions.

Note that with connection pooling, simply closing the connection without a rollback will only return the connection to the pool and the transaction will remain open until later reused or removed from the pool. This can result in locks begin held unnecessary and cause other timeouts and rolling blocks.

SET XACT_ABORT ON instructs SQL Server to rollback the entire transaction and abort the batch when a run-time error occurs. Compile errors (e.g. syntax errors) are not affected by SET XACT_ABORT.

In my experience, SET XACT_ABORT ON provides the desired behavior in most cases. I’ve never run into a situation where I wouldn’t want to rollback a transaction following a cancel or timeout. I nearly always specify SET XACT_ABORT ON in stored procedures that contain explicit transactions to ensure that transactions are rolled back even if the application code doesn’t clean up properly. The only time I don’t use XACT_ABORT is in rare cases where I need to trap and handle specific errors in Transact-SQL and continue.

I strongly recommend that SET XACT_ABORT ON be included in all stored procedures with explicit transactions unless you have a specific reason to do otherwise. The consequences of an application unwittingly performing work on a connection with an open transaction are disastrous.

SET XACT_ABORT ON is still needed with structured error handling. A client attention event (timeout or query cancel) will stop the executing code and the CATCH block will not be invoked. This could leave an open transaction unless the application code performs a rollback or closes the connection.
When you use XACT_ABORT with structured error handling, you should check XACT_STATE() in the CATCH block to determine whether or not COMMIT/ROLLBACK is possible

XACT_ABORT changes error handling behavior but doesn't change locking when no explicit transaction is specified. Without an explicit transaction, each statement is in an individual transaction that will be committed
or rolled back when the statement completes (successsfully or not) regardless of the XACT_ABORT setting. I think the only advantage of XACT_ABORT without an explicit transaction is that it can prevent the remainder of proc from continuing after an error even when the proc contains no error handling code:

CREATE PROC dbo.foo
AS
SET XACT_ABORT ON;
EXEC sp_executesql N'select * FROM MissingTable';
EXEC sp_executesql N'select * FROM AnotherMissingTable'; --never reached
GO
EXEC dbo.foo
GO