1. to set it globally : EXEC sys.sp_configure N'user options', N'16384' GO RECONFIGURE WITH OVERRIDE GO to test the if XACT_ABORT is on DECLARE @options INT SELECT @options = @@OPTIONS PRINT @options IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK' IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS' IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT' IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS' IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING' IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS' IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT' IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE' IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER' IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT' IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON' IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF' IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL' IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT' IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'

https://stackoverflow.com/questions/43192298/why-does-sql-server-default-xact-abort-to-off-can-it-be-set-to-on-globally

When a client timeout event occurs (.net CommandTimeout for example), the client sends an "ABORT" to SQL Server. SQL Server then simply abandons the query processing. No transaction is rolled back, no locks are released. Now, the connection is returned to the connection pool, so it isn't closed on SQL Server. If this ever happens (via KILL or client reboot etc) then the transactions+locks will be cleared. Note that sp_reset_connection won't or doesn't clear them, even though it is advertised to do so This detritus from the abort will block other processes. The way to make SQL Server clear transactions+locks on client timeout (strictly, ABORT events) is to use SET XACT_ABORT ON. You can verify this be opening 2 query windows in SSMS: 10.10.17

https://dba.stackexchange.com/questions/10912/sql-server-transaction-timeout/10929#10929

When a statement inside a transaction fails (for whatever reason) and XACT_ABORT is set to off, then… That transaction is abandoned. Any locks taken during that transaction are still held. Even if you close the connection from the application, .NET’s connection pooling will keep that connection alive and the transaction on SQL Server stays open. Fortunately, if someone reuses the same database connection from the connection pool, the old transaction will be rolled back. Unfortunately developers can’t count on that happening immediately. Abandoned transactions can cause excessive blocking leading to a concurrency traffic jam. Also, abandoned transactions can interfere with downstream solutions. Specifically ones that depend on the transaction log. Transaction logs can grow indefinitely. Replication solutions can suffer. If RCSI is enabled, the version store can get out of hand. Some (or all) of those things happened to us last week. Steps To Take Here are some things you can do: Do you have abandoned transactions right now? It’s not too hard to identify these abandoned transactions: -- do you have abandoned transactions? select p.spid, s.text as last_sql from sys.sysprocesses p cross apply sys.dm_exec_sql_text(p.sql_handle) s where p.status = 'sleeping' and p.open_tran > 0 Also if you use sp_whoisactive, you can identify these processes as those with a sleeping status and at least one open transaction. But there’s a trick I use to identify these quickly. The sql_text value in the output of sp_whoisactive will typically begin with CREATE PROCEDURE. When I see that, I know it’s time to check whether this connection is sleeping or not. SET XACT_ABORT ON Follow Dan Guzman’s advice to include SET XACT_ABORT ON in all stored procedures with explicit transactions. You can actually find the procedures in your database that need a closer look -- find procedures that could suffer from abandoned transactions SELECT * FROM sys.procedures where OBJECT_DEFINITION(object_id) like '%BEGIN TRAN%' and OBJECT_DEFINITION(object_id) not like '%XACT_ABORT%' order by name Set XACT_ABORT ON server-wide If you choose, you can decide to set the default value for all connections to your server. You can do that using Management Studio: 10.10.17

http://michaeljswart.com/2015/10/dont-abandon-your-transactions/

if you cannot set it on globally, than at least you can set it on your store procedure CREATE PROCEDURE MyProc AS SET XACT_ABORT ON BEGIN TRAN .... 10.10.17

https://stackoverflow.com/questions/8023183/how-to-set-set-xact-abort-on-in-a-sql-server-transaction

• Home
• Notebook
• Contact us

• Privacy Statement
• 

Copyleft ☠ 2024