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:

• How to get Total Row Count of all tables of a database?
• Understanding Page Split in SQL Server
• What should be the Best Value for Fill Factor?
• Top 21 New Features in SQL Server 2017 that is missing from SQL Server on Linux
• Read Why We should always Turn Off Database AutoShrink Database Property

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.

• Author
• Recent Posts

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.

Follow me:

Latest posts by Manvendra Deo Singh (see all)

• How to Fix SQL Error 1005: A Comprehensive Guide - April 9, 2023
• How to Fix SQL Server Error 207 – Invalid Column Name - April 9, 2023
• How to Fix SQL Error 1045: Resolving Access Denied Issues - April 8, 2023