DBA

The log scan number passed to log scan in database is not valid. This error may indicate data corruption

Advertisements

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 

 

Suspect_mode_repair

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.

 

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

Your email address will not be published. Required fields are marked *

5 + 5 =