Why does my Database get marked suspect after ALTER DATABASE SET READ_ONLY

Question

We still have databases running in 2008R2 100 compatibility mode. Every night we back up our OLTP databases and RESTORE them to a READ ONLY Server and set the database to READ_ONLY mode. We've been running this process for over 10 years. Recently, the job step that runs ALTER DATABASE [MY_DATABASE] SET READ_ONLY WITH ROLLBACK IMMEDIATE; after the RESTORE throws this error:

Msg 926, Level 14, State 1, Line 2

Database 'my database' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

Msg 3624, Level 20, State 1, Line 2

A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.

Msg 3313, Level 21, State 2, Line 2

During redoing of a logged operation in database 'my database', an error occurred at log record ID (0:0:0). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.

Msg 3414, Level 21, State 1, Line 2

An error occurred during recovery, preventing the database 'my datavase' (database ID 6) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

---

I can get it back to READ_WRITE, but WHY is this suddenly happening???

I don't think MS support will help because we are on a much older version. Any thoughts out there in the community will be greatly appreciated!!

Answer 1

Hi,

We still have databases running in 2008R2 100 compatibility mode.

Don't feel bad. I still have clients with SQL Server 2005 as well 😀

Recently, the job step that runs ALTER DATABASE [MY_DATABASE] SET READ_ONLY WITH ROLLBACK IMMEDIATE; after the RESTORE throws this error:

It is a bit hard to provide exact answer without checking the exact issue (the database) but in first glance (or first guess) it sound like there is a chance that your database is corrupted.

But did you notice this: "To check for database corruption, consider running DBCC CHECKDB."

The error which you get include multiple good suggestions/options. Have you tried any of these?

Without any more information and based on your description only, in your situation, I would probably start with running a check using DBCC CHECKDB on the original database which you backup.

Note! Founding that the database is corrupted might be much simpler then solve it. This can be a totally different story.

Note! In any case, I DO NOT recommend to made any attend to fix the database without someone with the right experience. In any case, make sure that you backup everything including all system databases as well before you do anything.

last note for noe: If one database is corrupted and the server exists so many years without proper checks then there is a good change you have more database(s). It is good to check all (again, after backup all)

Answer 2

Hi @Berman, Linda

When the database recovery fails due to corruption in the data files or log files, the database enters the SUSPECT state. In this state the administrator can choose the following operations.

(1) Do ALTER ONLINE again, and order SQL Server to do another recovery.

(2) Abandon the current database and restore the backup.

(3) Set the database status to EMERGENCY, and continue to try to repair the database.

For more details, you can refer to this link: https://www.stellarinfo.com/blog/recover-sql-database-from-suspect-mode/.

Best regards,

Aniya

Answer 3

This sounds very serious. The fact you cannot restore the read-only database is only a small problem. As Ronen suggests, it could be that you have corruption in the source database. Or it would be that the disk you are writing the backups to is faulty and corrupts the backup. Imagine now that you need that backup to restore the production database in case of a disaster!

You will need to test the path of 1) backup the database 2) Restore it. 3) Run a good DBCC on the restored copy. And as long it fails, you will need to try alternate targets for the backup disk and the server you are restoring to.