SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Know More About SQL Server Error number 6401

Introduction

The ACID acronym standing for Atomicity, Consistency, Isolation and Durability is the foundation 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.

About SQL Server Error 6401

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

Multiple rollback in the case of nested transaction is 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.

This error when encountered clearly specifies that the user has tried to do a 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 the Error 6401 can be prevented from occurring. They are:

  1. Use ROLLBACK TRANSACTION statement without specifying the name of the transaction.
  2. Replace BEGIN TRANSACTION statement with SAVE TRANSACTION statement.
  3. Use a 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.

Zora's SQL Tips

Hi! I am Zora Stalin, an IT geek and a passionate learner of technology. Besides my job as an Information Technology Analyst, I love searching and sharing new things that excite me help for others.

Comments

Leave a comment on the original post [sqlserveroverview.blogspot.com, opens in a new window]

Loading comments...