Found corruption error while running DBCC CHECKDB on the SQL Server DB.
Error messages
Msg 8990, Sev 16, State 1, Line 2 : CHECKDB found 0 allocation errors and 10 consistency errors in table 'VPX_EVENT' (object ID 138483572). [SQLSTATE 01000]
Msg 8989, Sev 16, State 1, Line 2 : CHECKDB found 0 allocation errors and 10 consistency errors in database 'VCDB'. [SQLSTATE 01000]
Msg 8958, Sev 16, State 1, Line 2 : repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (VCDB). [SQLSTATE 01000]
This is data loss corruption similar to Oracle’s datafile header corruption. Damage already been done. User can use repair_allow_data_loss feature as the SQL Server error suggested or restore from good backup. If backup available, I would prefer restoring the db or the specific table. The good news is, the VPX_EVENT isn’t a crucial table. It stores events related information.
Here is what can be done on the repair route. Another option is to clone the existing VCDB to a different MSSQL database and test out the procedures. If everything works smoothly (verified with no data loss), perform the same on the production side.
--If user decided to do it in existing db then take backup of the VCDB first
use VCDB
--verify the corruption still exist
DBCC CHECKDB
--this can only be done in single user mode. So, switch it to single user.
ALTER DATABASE VCDB SET SINGLE_USER;
--attempt with REPAIR REBUILD first where it will attempt to repair without data loss.
DBCC CHECKDB ('VCDB', REPAIR_REBUILD);
--To verify if corruption still exist.
DBCC CHECKDB;
--if it does, move on to repair allow data loss feature, this will result of masking out the corruption where there will unrepairable data loss.
DBCC CHECKDB ('VCDB', REPAIR_ALLOW_DATA_LOSS);
Once completed, set the db back to multi users, otherwise no one else can access the database.
ALTER DATABASE VCDB SET MULTI_USER;
DBA should find out what causes this corruption. This typically results of disk issues.
