Introduction

The ACID acronym standing for Atomicity, Consistency, Isolation, and Durability are the properties of SQL transactions. Transactions are used when the database has to be modified by using one or more SQL statements and to make sure the security of the database as well. However, at times, this normal functioning of the transactions may pose unexpected errors that disrupt the performance of transactions.

This blog is the outcome of several queries that I’ve come across regarding SQL Error 6401. Therefore, here is a complete article dedicated to SQL Server Error number 6401.

SQL Server Error 6401

While running transactions in SQL server, the user gets an option to nest them. However, care should be taken that the number of COMMIT TRANSACTION and BEGIN TRANSACTION should be same. The number of BEGIN TRANSACTION commands can be known by running global variable @@TRANCOUNT. SQL Server Error 6401 basically occurs during the rollback transaction of nested transactions. Rollback transaction enables user to bring back the SQL database in its original state. However, this is not possible when the Nested transactions are concerned.

Multiple rollbacks in case of nested transaction are not possible and hence when run, gives the following error:

Msg 6401, Level 16, State 1, Line 33 Cannot roll back Inner Transaction. No transaction or savepoint of that name was found.

The error encountered clearly specifies that the user has tried to do multiple rollback in nested transaction.

In the example, shown below, the user has created nested transaction in which the outer transaction is being denoted by outerTran and the inner transactions are denoted by innerTran

In the highlighted section, you will be able to see that a ROLLBACK TRANSACTION on the innerTran has been carried out. When this query is run, the SQL Server Error 6401 is encountered by the user.

How To Remove SQL Server Error 6401?

There are three possible ways by which Error 6401 can be prevented from occurring. They are as follows:

• Using ROLLBACK TRANSACTION statement without specifying the name of the transaction.
• Replace BEGIN TRANSACTION statement with SAVE TRANSACTION statement.
• Using third-party SQL Server Log File viewer software.

Conclusion

SQL Server Error number 6401 is a result of ROLLBACK TRANSACTION done in a nested transaction. The best method to avoid the error has been specified in the above section. In case, the error has already been encountered, the complete transaction will have to be run again and care should be taken while doing this.