In SQL Server, how do I know what transaction mode I'm currently using?

Question

Hi guys,

In SQL Server, how do I know what transaction mode I'm currently using? Such as autocommit, explicit, or implicit. And how can I change one mode to another using tsql? Great thanks.

Answer 1

select @@OPTIONS & 2

if this returns 2, you're in implicit transaction mode. If it returns 0, you're in autocommit.

BOL for @@OPTIONS

BOL for what each option is

To switch which mode you're in, you'd use

set implicit_transaction on

or

set implicit_transaction off

Answer 2

IF @@TRANCOUNT = 0 PRINT 'No current transaction, autocommit mode (default)'
ELSE IF @@OPTIONS & 2 = 0 PRINT 'Implicit transactions is off, explicit transaction is currently running'
ELSE PRINT 'Implicit transactions is on, implicit or explicit transaction is currently running'

I don't think there is a way to determine whether current transaction was started explicitly or implicitly. So, this code just tries to guess: if IMPLICIT_TRANSACTIONS is OFF, the transaction is assumed to be started explicitly.

MSDN references:

• @@OPTIONS function
• Numeric values for options
• @@TRANCOUNT

Answer 3

Slight modification to previously posted script - connection is in autocommit mode if there's no active transaction AND implicit transactions are off:

IF @@TRANCOUNT = 0 AND (@@OPTIONS & 2 = 0)
  PRINT 'No current transaction, autocommit mode (default)'
ELSE IF @@TRANCOUNT = 0 AND (@@OPTIONS & 2 = 2)
  PRINT 'Implicit transactions is on, no transaction started yet'
ELSE IF @@OPTIONS & 2 = 0 
  PRINT 'Implicit transactions is off, explicit transaction is currently running'
ELSE 
  PRINT 'Implicit transactions is on, implicit or explicit transaction is currently running' + CAST(@@OPTIONS & 2 AS VARCHAR(5))