Return to Revisions

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]