Transaction Log Cannot Be Truncated After Memory-Optimized Filegroup Removal

Question

Hello there,

One of our clients is facing an issue with a database where the memory-optimized filegroup was forcibly removed using DBCC CHECKDB (test, REPAIR_ALLOW_DATA_LOSS). The database remains functional, but the transaction log continues to grow and cannot be truncated.

A few days ago, the database was part of an Always On Availability Group. Since log truncation was not possible, we removed it from Always On and switched to SIMPLE recovery mode. We executed these commands to shrink the log but issue still exist:

USE [test]

GO

DBCC SHRINKFILE (N'test_log', 0, TRUNCATEONLY)

GO

USE [test]

GO

CHECKPOINT

DBCC SHRINKFILE (N'test_log', 0, TRUNCATEONLY)

GO

We backed up and restored the database on another server, ran DBCC CHECKDB ('test', REPAIR_REBUILD), and tried to truncate the log again, but the issue persisted.

In the final attempt, we deleted the log, created a new one, and ran DBCC CHECKDB (test, REPAIR_ALLOW_DATA_LOSS) again. This made the database worst, now db is functional and while executing backup is gives and error.

The log scan number (463443:58:2) passed to log scan in database 'test' 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. (Microsoft SQL Server, Error: 9003)

Is there a solution to this?

Any insights or suggestions would be greatly appreciated.

Answer 1

Hi @kedar giri

One of our clients is facing an issue with a database where the memory-optimized filegroup was forcibly removed using DBCC CHECKDB (test, REPAIR_ALLOW_DATA_LOSS).

Referring from this doc: The Memory Optimized Filegroup.

Once you use a memory-optimized filegroup, you can only remove it by dropping the database. In a production environment, it is unlikely that you need to remove the memory-optimized filegroup. You can't drop a non-empty container or move data and delta file pairs to another container in the memory-optimized filegroup.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Yup. Cosmog is spot on it. Throwing the memory-optimised filegroup sent you out in the land of the unsupported. Nevermind that DBCC did it for you. REPAIR_WITH_DATA_LOSS is dangerous.

I think your best way forward is to build a new database from scripts and copy data over as much as you can. Possibly, you could do this with a BACPAC, but since you have corruption, there may be places where it fails.

By the way, corruption does not occur out of the blue, but is usually due to faulty hardware. So there is all reason to investigate and if possible move to new hardware.