Repair Database with CHECKDB

Repair the database with allow data loss. We will need to set the database into single user mode, run the repair and then set the database back into multi user mode.

Check Database

DBCC CHECKDB('CorruptionTest')

-- Low overhead check of the physical consistency of the database
DBCC CHECKDB('CorruptionTest') WITH PHYSICAL_ONLY

Repair Database

USE master;
ALTER DATABASE [CorruptionTest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

DBCC CHECKDB ('CorruptionTest', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO

ALTER DATABASE [CorruptionTest] SET MULTI_USER;
GO

Check Repair Database Progress

SELECT  
	session_id ,
	request_id ,
	percent_complete ,
	estimated_completion_time ,
	DATEADD(ms,estimated_completion_time,GETDATE()) AS EstimatedEndTime, 
	start_time ,
	status ,
	command 
FROM sys.dm_exec_requests
	WHERE database_id = 7

DBCC CHECKDB WITH PHYSICAL_ONLY

One alternative to minimize contention, is to use the WITH PHYSICAL_ONLY DBCC CHECKDB option. This option limits the processing to checking the integrity of the physical structure of the page and record headers in addition to the consistency between the pages for the allocation structures (data and indexes).  This option is specifically designed to provide a low overhead check of the physical consistency of the database.  This check also detects torn pages and common hardware failures that can compromise a user’s data. Perhaps you would run this more frequently and then schedule a full DBCC CHECKDB during periods of low activity. The full run of DBCC CHECKDB will take considerably longer to complete, due to the more comprehensive logical structure checks. 

Running the DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS command might require deleting some database pages, resulting in loss of data. Also, DBCC CHECKDB commands might fail and return an error when dealing with severely corrupt SQL database files.

1. Besides REPAIR_ALLOW_DATA_LOSS repair mode, DBCC also offers two other repair modes:
   • REPAIR_FAST: This repair option does not perform any repair actions. It helps maintain syntax for backward compatibility.
   • REPAIR_REBUILD: The REPAIR_REBUILD option helps repair the database without any data loss. It can be used to repair missing rows in nonclustered indexes, and for rebuilding an index.
2. While the REPAIR_ALLOW_DATA_LOSS repair option helps in repairing all reported errors in the SQL server database, it is not the best option for repairing database corruption. This repair option causes data loss. In fact, Microsoft recommends using the REPAIR_ALLOW_DATA_LOSS option as a last resort when you cannot restore a database from the backup. If you do not have a backup and cannot risk losing data, use a specialized MS SQL repair software to repair the database without any loss in database integrity.

Note:

If database is part of AlwaysOn group, need to remove from AlwaysOn group.

Try restarting the SQL Server services before repair to see if that fixed the database errors.

Sources:

https://www.mssqltips.com/sqlservertip/5645/sql-server-database-corruption-and-impact-of-running-checkdb-repair-with-allow-data-loss/

How to Repair SQL Database using DBCC CHECKDB Command