Do I really need to use “SET XACT_ABORT ON”?

by

Remember that there are errors that TRY-CATCH will not capture with or without XACT_ABORT.

However, SET XACT_ABORT ON does not affect trapping of errors. It does guarantee that any transaction is rolled back / doomed though. When “OFF”, then you still have the choice of commit or rollback (subject to xact_state). This is the main change of behaviour for SQL 2005 for XACT_ABORT

What it also does is remove locks etc if the client command timeout kicks in and the client sends the “abort” directive. Without SET XACT_ABORT, locks can remain if the connection remains open. My colleague (an MVP) and I tested this thoroughly at the start of the year.

More Related Contents:

  1. Check if table exists in SQL Server
  2. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  3. Simulation of CONNECT BY PRIOR of Oracle in SQL Server
  4. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  5. SQL Server – transactions roll back on error?
  6. Creating audit triggers in SQL Server
  7. Regular Expressions in SQL Server servers?
  8. How do you specify a different port number in SQL Management Studio?
  9. TSQL Try / Catch within Transaction or vice versa?
  10. Diagnosing Deadlocks in SQL Server 2005
  11. Disadvantages of MARS (Multiple Active Result Sets)?
  12. Aggregate bitwise-OR in a subquery
  13. Check if a temporary table exists and delete if it exists before creating a temporary table
  14. Replace single quotes in SQL Server
  15. Drop primary key using script in SQL Server database [duplicate]
  16. sql use statement with variable
  17. What happens if you don’t commit a transaction to a database (say, SQL Server)?
  18. Decimal values in SQL for dividing results
  19. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  20. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  21. How to store directory / hierarchy / tree structure in the database?
  22. Date range overlapping check constraint
  23. INFORMATION_SCHEMA vs sysobjects
  24. Bulk insert fixed width fields
  25. Does query plan optimizer works well with joined/filtered table-valued functions?
  26. Get row where datetime column = today
  27. Table Valued Function Killing My Query Performance
  28. Full text catalog/index search for %book%
  29. @@ERROR and/or TRY – CATCH
  30. SQL Server stored procedure return code oddity

Leave a Comment