How to Disable AutoCommit in SQL Server?

Autocommit mode is the default transaction management mode of the SQL Server Database Engine. Every Transact-SQL statement is committed or rolled back when it completes. If a statement completes successfully, it is committed; if it encounters any error, it is rolled back.

Enable or Disable Autocommit using T-SQL

You can turn off auto commit by setting implicit_transactions ON. Run below T-SQL command to turn off autocommit in SQL Server.

SET IMPLICIT_TRANSACTIONS ON

When the setting is ON, it returns to implicit transaction mode. In implicit transaction mode, every change you make starts a transactions which you have to commit manually. When you need to enable it just run above command with OFF clause. Run below command to enable auto commit.

SET IMPLICIT_TRANSACTIONS OFF

autocommit is the default for Sql Server 2000 onwards.

Enable or Disable Autocommit using GUI

You can also turn off autocommit using SQL Server Management Studio by following below steps.

  1. Connect to SQL Server Instance in SQL Server Management Studio.
  2. From the Menu bar, click on Tools and then choose Options
  3. Select Query Execution then SQL Server followed by ANSI
  4. Make sure to click on check box SET IMPLICIT_TRANSACTIONS
  5. Click on OK

Now, open a new Query window and start executing the scripts. If you want to turn it on the follow same steps as given above and uncheck the SET IMPLICIT_TRANSACTIONS option that are mentioned in step no 4.

Related Articles:

I hope you like this tip. You can drop your questions and feedbacks in comment sections. Also you can follow our facebook page and Twitter handle to get latest updates.

Manvendra Deo Singh
Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *