SQL Server database corruption is troublesome for DBAs, particularly if the situation arrives suddenly and there is no backup. This situation can lead to prolonged downtime and permanent data loss in the absence of a suitable database repair solution. Knowing the reasons for SQL database corruption can help diagnose the root cause and fix it.
This article provides an in-depth understanding of SQL database corruption issues and the methods to repair the corrupted database. It also outlines a SQL Server database repair tool as an alternative to DBBC CHECKDB with REPAIR_ALLOW_DATA_LOSS argument.
Types of SQL Server Database Corruption
SQL page-level corruption: page-level corruption occurs when the information or data stored in the header, body, or slot array of a database page is altered such that the user cannot access the page. Page-level corruption can happen due to reasons like hardware issues, disk/sub-system failure, malware, faulty updates and patches, etc.
Boot page corruption: this is a more critical case of SQL database corruption as it concerns the boot page. There is only one boot page per SQL database, and it stores the metadata for the entire database. So, its corruption can affect the whole database file. Further, DBCC CHECKDB or page-level restore cannot fix the boot page corruption. This limitation is given the fact that the boot page stores Meta information like the current version, database ID, checkpoint LSN, etc.
Non-clustered index corruption: this type of corruption is associated with SQL Server 2008 and later versions. It typically occurs when a SQL DBA attempts running a complex UPDATE statement with NOLOCK hint against a table. Non-clustered index corruption leads to incorrect reading of the SQL database query or multiple read operations on the same value.
SQL database in suspect mode: SQL database suspect mode is a frequent issue DBAs face due to damage in the primary file group file, which stops the database recovery during SQL Server startup. A SQL Server may mark the database in SUSPECT mode after detecting a problem in the log file due to reasons like hardware malfunction, disk space issue, system crash, etc. A database in suspect mode fails to perform the read/write operations leading to downtime.
How to Repair a Corrupted SQL Server database with the DBCC CHECKDB Command
DBCC CHECKDB is a set of T-SQL commands to check the logical and physical integrity of SQL database objects in a SQL Server database or Azure SQL database. You can run these commands on the entire database, individual table and views in the database, or catalog.
The following is the general syntax of the DBCC CHECKDB command:
The following is a description of REPAIR_FAST, REPAIR_REBUILD, and REPAIR_ALLOW_DATA _LOSS arguments:
Using DBCC CHECKDB for SQL Database Repair
REPAIR_ALLOW_DATA_LOSS argument with CHECKDB must be used only in an emergency and as the last option. It can fix errors that involve deallocating a row or pages, but may result in data loss, as the deallocated data is lost and the extent of this loss cannot be determined.
The following are the steps to run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS command:
1. Create physical copies of the database, including the primary and secondary data file, transaction log file, full-text catalogs, file stream, etc.
2. Set the database to single-user mode using Transact-SQL
|
Important notes:
|
a) Connect to the T-SQL Database Engine, and click New Query
b) Run the ALTER DATABASE command as follows:
USE master;
GO
ALTER DATABASE SoundBox2020
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE SoundBox2020
SET READ_ONLY;
GO
ALTER DATABASE SoundBox2020
SET MULTI_USER;
GO
The above command switches the database named SoundBox2020 to single-user mode. WITH ROLLBACK IMMEDIATE argument is used to allow roll back of all incomplete transactions.
c) Run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS argument, as follows:
DBCC CHECKDB(‘SoundBox2020’, REPAIR_ALLOW_DATA_LOSS)
GO
The result of the command will look similar to this:
DBCC results for 'SoundBox2020'.
Repair: The page (1:166) has been deallocated from object ID 2121058256, index ID 0, partition ID 72057594039042048, alloc unit ID 72057594043301888 (type In-row data).
Msg 8928, Level 16, State 1, Line 1
Object ID 2121058256, index ID 0, partition ID 72057594039042048, alloc unit ID 72057594043301888 (type In-row data): Page (1:166) could not be processed. See other errors for details.
The error has been repaired.
There are 930 rows in 14 pages for object "SoundBox2020".
SQL Database Repair Tool Could Save Your Business
Considering the complexity of SQL Server database and the different types of errors and corruption it can face, it is imperative to maintain an up-to-date backup. Backup restoration provides the safest strategy to restore the database. You can also consider a SQL database repair tool to meet contingencies like no backup or outdated backup, or when the corruption is beyond what REPAIR_REBUILD can repair. The fact is that corruption scenarios concerning boot page, log file and non-clustered indexes, etc. are widespread and can be incredibly complex, largely unresolvable using any built-in repair command.
Having an enterprise database repair tool can plug this gap in your SQL database maintenance and restoration strategy, empowering you to efficiently and safely address the downtime scenarios. For instance, Stellar Repair for MS SQL is an advanced SQL database repair software package for repairing corrupted MDF and NDF files for SQL Server 2019, 2017 and older versions without any data loss. It can also extract and restore the database from a corrupted backup file. So, considering a tool such as this can save the day even if a well-maintained database copy turns corrupt.
