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.
- Connect to SQL Server Instance in SQL Server Management Studio.
- From the Menu bar, click on Tools and then choose Options
- Select Query Execution then SQL Server followed by ANSI
- Make sure to click on check box SET IMPLICIT_TRANSACTIONS
- 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.
- 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
Latest posts by Manvendra Deo Singh (see all)
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018