SQL SERVER – Running CHECKDB with Minimum Repair Level

“How do I run CHECKDB with Minimum Repair Level?”

When you run a blog with a million views a month, one must expect every kind of audience on the blog and author should be not judge every single email. It is almost impossible to correct the internet and there are times when we have to accept the hard fact that some user wants shortcuts and want to learn things the hard way. Well, here here an email I received yesterday.

“Hi Pinal,

My database is showing me consistency errors when I run CHECKDB. The matter of the fact, at the end of the error, it clearly gives me guidance about what should I do. However, every time when I follow the guidance of SQL Server, I am not able to complete the task what should I do.

Here is the error message and guidance.

CHECKDB found 0 allocation errors and 39 consistency errors in database ‘MegaMart-AWS’.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MegaMart-AWS).

Now what is the correct way to run DBCC CHECKDB with minimum repair level.

– Mark”

I immediately replied to him with following questions –

“Hi Mark,

I am currently driving and I will reach home in next 45 minutes.

First of all, do you have a good backup of your database? Before you run any DBCC command, it is a good idea to validate that you have a good backup which was before errors. If due to any reasons, you do not have a good backup, have you taken a backup of your system as you are planning to run the DBCC command which can potentially create some data lose as we do know where exactly the error is.

I have few more questions, but let me know the answer to the above question and meanwhile I will send you a systematic checklist once I reach home in 45 minutes.

~ Pinal”

After answering in brief while standing at the stop signal, I was checking my my other emails and soon I received following response.

“Hi Pinal,

I just want you read my email again and answer my question. What is the command to run DBCC CHECKDB with minimum repair level. Please do not ask me more question as I am not expecting consultancy from you.

– Mark.”

Honestly, I was not even thinking about consultancy but I am now a day comfortable with such emails. Some people call it rude, but I think I have come to a position where I call it direct and to the point email. Well, some people want to learn and some people want to stay safe while changing anything in the database, whereas some people just like to take risk :-)

I immediately replied with following response-

“Hi Mark,

Here is the command you requested –

USE [master];
GO
ALTER DATABASE MegaMart-AWS SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB(N’MegaMart-AWS’, REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE MegaMart-AWS SET MULTI_USER;

Best wishes

~ Pinal “

After I reach home, I forgot about this experience. Next day, I received an email from Mark.

“Hi Pinal,

Thanks for your suggestion. Your script did the task.

– Mark”

I guess, the story ends here!

Reference: Pinal Dave (https://blog.sqlauthority.com)