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

Question

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

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))

Answer 2

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_transactions on

or

set implicit_transactions off