2

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
Improve this question
2
  • 2
    Well... brentozar.com/archive/2016/04/…
    – Erik Darling
    Jan 30, 2017 at 0:28
  • At this point, you've essentially nuked everything with repair_allow_data_loss so I wouldn't put any more time into it TBH. Restore from your backups... I hope they don't have any corruption in them.
    – Sean Gallardy
    Jan 30, 2017 at 2:42

2 Answers 2

Reset to default
1

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.

Improve this answer
0

I totally agree with Sean Gallardy. Restore from the updated backup. Read more about suspect database mode from here: http://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.