Share via

SQL Server Troubleshooting: Could not run begin transaction in database because the database is in bypass recovery mode

  • Article

Introduction

You might know that an adaptive Server helps a lot in making transactions in a database. It automatically brings about all data variations and adjustments, even it makes one step change request itself, taking it as a transaction. In the scenario of taking out transactions, there comes the importance of recovery mode. Bypass recovery mode makes the adaptive server to not recover one or more databases. Here we will discuss bypass recovery mode and how to fix errors of the database when it is in bypass recovery mode.

Error message

Could not run begin transaction in database because the database is in bypass recovery mode

Summary

What does an adaptive server do?

First, we need to understand that what does an adaptive server do. Besides dealing with all commands related to data modification, an adaptive server by default manages all insert, update and delete statements considering them a single transaction. An adaptive server deals with different sorts of transactions. This transaction might be a

  • Local – It is generated in response to local command.
  • Internal – it is used by the server itself.
  • External – it is generated by an external user or a client like you can say a TP Monitor.
  • Subordinate – It is also a child transaction that runs in parallel with a parent transaction.

What is a bypass recovery mode?

Bypass recovery mode makes the adaptive server to not recover one or more databases. The thing that happens in this mode is that you can access your database although it is not recovered. This mode can be useful as you can permit access to your database for analysis of problems or copy out your precious data and many more.

Why does this error happen?

This error is also named Error 3908 that happens when a user tries to fulfill a transaction while the database is in bypass recovery mode. However, there are certain transactions that are allowed in this mode but no transactions “regardless of type” can be done when your database is in bypass recovery mode. The root cause of this error might be the corruption of your database.

This error is due to the following reasons: -

  1. Whenever an attempt is made to initiate a transaction when your database is not available for update. This reason is applicable to all kinds of transactions, whether the transaction is of the update, delete, insert, or any kind of transaction.
  2. Whenever an attempt is made to initiate a transaction which is a subordinate one while the database is available for the updates. A subordinate transaction is a transaction that is a child transaction and runs in parallel with a parent transaction.

How to fix the error?

First, never try to initiate a transaction whenever your database is in bypass recovery mode. Always check that if there are any automated jobs have already submitted a batch job that has tried this transaction before. You must restore your database to its original status of working that it had, former to the status of bypass before trying to fulfill any transaction.

Use the below-mentioned steps and reset the status of the database. You must know that the status of zero is quite appropriate, but it is not the only possible status value that you may use. Always reset your database to the original or normal status that it had, preceding to the entering status of the bypass.

You need to make the status of your database as 0 database status. It is shown below:

Sp_configure “allow updates”, 1
GO
BEGIN TRAN
Go
Use master
GO
Update sysdatabase set status=0
Where name=your_db
GO

First, you need to verify the above update command to get affected only in one row. In case when more than one rows get affected by the above command then use a rollback transaction to help you out. After that, commit the transaction to done with error and shut down the adaptive server. This is shown below:

COMMIT TRAN
GO
SHUTDOWN WITH  NOWAIT
GO

Doing this procedure, the server will be restarted, and it will attempt to recover your database. When you are done, never forget to allow your updates option back to’false’. If this error is still there, then ensure whether you started your server in maintenance mode. All databases are in bypass recovery mode. Also, ensure that there were no trace flags(-T) specified before initiating the server.

  1. You can also restart the adaptive server.  For this, run DBCC CheckDB and DBCC Checkalloc on your database affected by this error, to ensure that there are no more errors. It is highly recommended; in case you want to make your database free of errors.

There is another way to recover a database in this situation you can use Stellar Repair for MS SQL. This tool allow you to repair corrupted databases.

Conclusion

Monitor your transactions. Whenever your transactions are not carrying out because the database is in the bypass recovery mode, it may point towards the database corruption. Follow the above steps to get rid of this error. Keep in mind, this bypass recovery mode in database can occur in all versions as an error.