Return to Revisions

!!! Please use this method in case of corrupted database that contains memory optimized table (you can't use DBCC check on databases that contains memory optimized tables) !!!

WARNING: This is an unsupported operation. There have been reports of unrecoverable log corruption when using this.

It's a hack method and it's risky. Microsoft didn't verified this solution

Before doing anything take backup

then

1. Delete memory optimized tables

2. Detach Database

3. Create new database with same files without memory optimized filegroup

4. Modify database files and change it to detached database (mdf,ldf,ndf) files

   alter database test1 modify file 
       (
       name='test1' , 
       filename='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\test.mdf'
       )
   
   alter database test1 modify file 
       (
       name='test1_log', 
       filename='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\test_log.ldf'
       )
   

5. Now try to to repair database:

   alter database test1 set emergency
   
   alter database test1 set single_user with ROLLBACK IMMEDIATE;
   
   dbcc checkdb(test1,repair_allow_data_loss)
   
   alter database test1 set multi_user
   
   alter database test1 set online
   

6. After successfully repair database remove memory optimized filegroup from database

   ALTER DATABASE [test1] REMOVE FILEGROUP [memory_optimized_filegroup_0]
   

Follow steps correctly for prevent log corruption

Step 4: Alter log file name must be done correctly Step 5: DBCC is Important to prevent corruption

Also for getting best result:

Don't use database while doing steps (Insert - Update - Changing Schema)

do CHECKPOINT to flush log to data file then shrink log file

you will never have log corruption issue