2

I know that while the database is marked as suspected users cannot start new transactions, and the only access is via emergency mode which gives only read only access. I want to know how are transactions handled in this case

  • firstly when a database is marked as suspected
  • secondly when we use emergency mode

Are they rolled back normally?

Thanks.

2

firstly when a database is marked as suspected

There may be other reasons but the most common one is when you have some issue with data file or log file of database and it goes through crash recovery after SQL Server is restarted, since SQL Server will not be able to recover the database due to corrupt, incorrect or missing information in one of the files(data or log) it has to mark database as suspect. Learn how to make database suspect (only for testing purpose. It goes without saying that please do not try this on Prod and even UAT).

secondly when we use emergency mode.

In any case where you want to gain access to contents of database but are not able to because it has been marked suspect. You can then put database in emergency mode which is "inconsistent database" provided to you without fully doing crash recovery. Please note you might not always be successful.

Are they rolled back normally?

When you use emergency mode you have lost all possibility to bring database online so you force it to "kind of come online with inconsistent data" using emergency mode. When you do so no recovery happens and you are just given read access to inconsistent database. After this when you run checkdb with repair_allow_data_loss then all kinds of actions are taken to bring database online in consistent state, to do so repair_allow_data_loss will remove any such thing which might block database to come online.

Putting database in emergency and then recovering it should not be treated as method to solve suspect database issue. Restoring from good backup should be the preferred way.

Would also suggest you to read Paul Randal blog

0

At first I would like to define what is transaction means: A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

when a transaction is sent to Database server it will reserve some memory, locate database location, try to access it, then start searching for data location inside the database.

SQL Server database go to suspect mode due to many reasons below are the most common ones, if any major issue happened to the database it will become fully\partially inaccessible and SQL server can't start on transaction on it.

  • Hardware failure
  • Improper shutdown of the database server
  • Corruption of the database files
  • Unavailable device files: if the file that
  • Unavailable database files
  • Database resource used by operating system

SQL Server emergency mode (repair mode to fix "inconsistent database") is used to recover the database from any failure if the database is accessible, since the database is repair mode you can execute you can edit on some system tables, and read from normal tables to make sure database will not have any issue after repair.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.