SSMS Configure Auto Commit

Introduction

This is required to understand that when we work on SQL Server Management Studio, all the DML operations are directly done on database and if any unexpected command is executed, one can experience data loss permanently.

To recover from this situation, one has to restore the backup, but this has its own problems:

1. Database will be set back to the state where previous backup was done; this will result in loss of the changes done after the backup was taken.
2. Need to contact DB admin to perform the backup restore.
3. Whole database outage before restore completes and many more…

Using the Code

To overcome this problem, we can execute all our queries in a system created transaction per session; to make this setting work, you need to make the following changes in your SSMS:

Configure Auto-commit in SSMS

1. Connect to your SQL Server using SSMS
2. From the Menu bar, select Tools –> Options-> Query Execution –> SQL Server –> ANSI
3. Set SET IMPLICIT_TRANSACTIONS checked to open the Transaction automatically for you when you open a new query tab.
4. If you are sure, type “Commit Transaction” to commit your changes.
5. If anything gone wrong, type ”Rollback Transaction” and you are safe; all changes made on the tab (i.e. current session) will be reverted.

Pros

Avoid accidental DML action

Cons

Transactions are open, so table may get locked for other users; until they are committed/rollback.