Database went into suspect mode database corruption
One of our servers got migrated from one data center to other data center using third party tool called Zerto.
After migration two databases went into suspect mode.
Errors in the log:
The log scan number (40359:103:2) passed to log scan in database <db name> is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup
During undoing of a logged operation in database <db name> , an error occurred at log record ID (0:0:0). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
An error occurred during recovery, preventing the database <db name> 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
If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup
We do not have any replication configured. But we enabled the CDC change data capture that got an error for the CDC https://repltalk.com/2014/07/22/sql-server-profiler-showing-9003-exception-when-cdc-is-configured/
I have tried usual check for suspect mode database and understood there is zero error reported from DBCC CHECKDB (N’DB_name’) WITH ALL_ERRORMSGS, NO_INFOMSGS;
I was having backup as well. I thought of fixing this by allow loss in case of any report I can restore the database from backup as well. Since there is no error reported from CheckDB command.
SELECT NAME,IS_CDC_ENABLED FROM SYS.DATABASES ALTER DATABASE [DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE [DB] SET EMERGENCY; -- THIS WILL SHOW THE ERROR AND CURRUPTION DBCC CHECKDB (N'DB_NAME') WITH ALL_ERRORMSGS, NO_INFOMSGS; -- THIS WILL ALLOW DATA LOSS DBCC CHECKDB (N'DB', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS; ALTER DATABASE [DB] SET MULTI_USER; BACKUP DATABASE <DB NAME> TO DISK ='' SELECT IS_TRACKED_BY_CDC,* FROM SYS.TABLES
I have repaired the database and restored the backup as another database and verified all tables and count of rows both database are identical.
In my case, there is no data loss. I believe there were some issues with CDC tracking. If anyone facing this please comment on it.
