Is Your SQL Server Database Need to Repair?

Run below command to check whether your SQL server database needs to be repaired or not

DBCC CHECKDB (Database-Name) with no_infomsgs

• DBCC CHECKDB: It checks all the logical & physical integrity on the specified SQL server database.
• Database-Name: Just replace the 'Database-Name' with your database name for which to run DBCC CHECKDB integrity. If you not specified the database name then current database will be used.
• No-Infomsgs: It will remove all the information messages

If above command executed without any error message then there is no need to repair SQL server database but if there is error message(s) then you need to repair your SQL server database.

Note: If error message appears with DBCC updateusage then you need to run DBCC UPDATEUSAGE command on the database.

DBCC UPDATEUSAGE: It detects and corrects all the table related problems like rows, data page count, reserved page, used page etc. in specified database. Run below command to resolve table related problem in the database.

DBCC UPDATEUSAGE ({database_name} {table_name}) with no_infomsgs

If appears error message does not contain dbcc updateusage then you need to repair your SQL server database.

Why Need to Repair?

You can see the reasons from the Windows system & application event logs. SQL server logged possible cause for the problem in the windows system logs.

Suggestion: I will recommend you to check your database backup before repairing the corrupt SQL server database. If Backup is clean & updated then restore it.

How to Repair?

If backup is not available then you need to repair your SQL database. SQL server provides following repair options:

• Repair_Rebuild: SQL server suggests it when there is no any possibility of data loss from the specified database.
• Repair_Fast: SQL server suggests it when no repair is performed
• Repair_Allow_Data_Loss: It will try to fix all the error messages with possibility of some data loss.

To perform any of the repair options, you need to put your database in single user_mode:

ALTER Database <database-name> SET Single_User

Here is the syntax of all repair options:

DBCC CheckDB ([YourDatabase], REPAIR_REBUILD)

DBCC CheckDB ([YourDatabase], REPAIR_FAST)

DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)

After repairing your database, don't forget to set your database in multiple user_mode. You can do this by running following command:

ALTER DATABASE [YourDatabase] SET MULTI_USER

What to Do after Repairing the Database?

After repairing your corrupt SQL server database, you need to do following things:

• Re-Run DBCC CHECKDB: It is very important to re-run DBCC CHECKDB command on the database after repairing. If it comes out without any error message then it is conform that there is no any inconsistency in the database.
• Make Full Backup of Database: If DBCC CHECKDB comes out cleanly then take a full backup of your database and keep it at safe place. Backup will help you in restoring of database.
• Test Your Backup: Verify your backup by restoring it on another server. Successful restorations can only guarantee that your backup is consistence.
• Make Disaster Recovery Plan for Future: Make a proper disaster recovery plan for future.