10

I understand the purpose of SET XACT_ABORT command:

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.

In general, the scenarios where one would want to continue processing a transaction if error would be outnumbered by the scenarios where one would want to rollback the entire transaction if error, because I often hear DBAs and blogs recommending to keep SET XACT_ABORT ON to avoid running into inconsistent transaction results. I have seen all our stored procedures also have SET XACT_ABORT ON, as part of the templated-code.

Quesions:

  1. If most of the use cases require XACT_ABORT to be ON, what would have lead SQL Server to default it to OFF? Would defaulting XACT_ABORT to ON had added any overhead to SQL Server transaction processing?

  2. The SET XACT_ABORT command affect only the current sessions. I know I can make SSMS default to SET XACT_ABORT ON from SSMS > Tools > Options > Query Execution > SQL Server > Advanced as shown in this image: enter image description here But this is limited to only for the SQL statements running through SSMS and does not help with stored procedures called through application code/SSIS packages . For those procedures I still have to repeat SET XACT_ABORT ON in every procedure. Is there any way XACT_ABORT can be set to ON at database level? Any other ways I can set the XACT_ABORT globally and don't have to worry about it every time?

HappyTown
  • 6,036
  • 8
  • 38
  • 51
  • You can issue a SET XXX ON/OFF over the active connection whenever you like and the option will apply to the whole session – Alex K. Apr 03 '17 at 19:07
  • [Why isn't SET XACT_ABORT ON the default behavior?](http://stackoverflow.com/questions/7956856/why-isnt-set-xact-abort-on-the-default-behavior) – Alex K. Apr 03 '17 at 19:08

1 Answers1

8

You can set XACT_ABORT ON as a global default connection setting at the server level, although the command is a bit obscure:

EXEC sys.sp_configure N'user options', N'16384'
GO
RECONFIGURE WITH OVERRIDE
GO

See here for details.

The option can also be set through SSMS Object Explorer > Server Properties > Connections:

enter image description here

Ed Harper
  • 21,127
  • 4
  • 54
  • 80