Today my database seems suspect. I am running SQL Server 2016 with memory tables. The below code won't work to fix the suspect. SQL Server keeps saying I have to restore. Any ideas?
EXEC sp_resetstatus dbname;
ALTER DATABASE dbnameSET EMERGENCY
DBCC checkdb(dbname)
ALTER DATABASE dbnameSET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (dbname, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE dbnameSET MULTI_USER
2 Answers 2
Sadly, corruption checking doesn't work for In-Memory OLTP (Hekaton).
Your best bet is to restore from backup at this point, and then maybe upvote the request for Microsoft to get corruption checking to work. Maybe someday you'll be able to check for corruption on your important data before you're stuck getting surprised like this.
Mine was suspect because I disabled the resource governor. I had a resource pool for in memory processes and when the server rebooted it failed to find the resource pool (as it was disabled). I re-enabled the governor and restarted the sql server service and it was resolved. I am fine with the governor being enabled (prefer it not at this point due to.. circumstances.. but its not a deal breaker) but I am sure there is probably a way to get the startup to not look for such a resource pool... Good luck on that route if future readers are in this boat :) Hope this helps.
Also, be careful doing "normal" recovery options when you have an in memory filegroup in the DB. Things can get super screwed very easy and your only hope ends up being a successful restore. If you are not 100% confident your backup is intact and will succeed, proceed with extreme caution. In memory is still pretty funky even 8 years after the original post :)