How we check the integrity of databases and how we fix any corruption in SQL Server
Certifications:
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
- How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024
In a database there is always the possibility of having one corruption. This can be either physical e.g. from some disk damage or either logical e.g. wrong values in a table field. In the article we will see how we can detect it and how to fix it.
When we go to access data that has corruption an error message will be returned. But if we don't call this data often, we will be slow to detect it. This will make it more difficult to fix.
How do we find out if a database shows any corruption?
With the transact SQL command CHECKDB and setting the name of the base we check for any type of corruption.
DBCC CHECKDB (test_db)
Once it is completed it will show us the result, or we can see at any time from the errorlog by running the following command:
exec xp_readerrorlog 0,1,"CHECKDB",null,null,null,"DESC"
Its result will be something like this, we see that in this case it says that it found 0 errors.
DBCC CHECKDB (test_db) executed by dbadmin found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds. Internal database snapshot has split point LSN = 0000002d:000012be:0001 and first LSN = 0000002d:000012bc:0001.We can give parameters to the CHECKDB command such as not to display informational messages, to lock the entire table to run faster without using tempdb, to use up to 2 cpu cores, etc. :
DBCC CHECKDB (test_db) WITH NO_INFOMSGS,TABLOCK,MAXDOP=2;
How do you fix corruption
Depending on what type of corruption we have, we follow different steps.
As we said at the beginning of the article, a physical or logical corruption, from then on, you can also divide into other categories such as:
- Page Corruption: a specific page has suffered damage that we can restore individually as we have mentioned in detail in an article here.
- Non-Clustered Index Corruption: which since it does not contain actual data we can simply drop and recreate it.
- Clustered Index Corruption: in which extensive damage has been done to our real data and there we can try to run checkdb with repair and if it doesn't work we will be forced to restore the entire database somewhere in order to get the tables we lost.
Checkdb with Repair
To try to fix the error without losing data, run the following:
USE master;
GO
ALTER DATABASE test_db
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB ('test_db', REPAIR_REBUILD) WITH NO_INFOMSGS,MAXDOP=0;
GO
ALTER DATABASE test_db
SET MULTI_USER;
GO
In case the problem is not fixed we can run it with the parameter repair_allow_data_loss. But there is a chance of losing data, that's why you recommend using with begin transaction first and then if we see that our data is OK to do commit;
USE master;
GO
ALTER DATABASE test_db
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
--BEGIN TRANSACTION
DBCC CHECKDB ('test_db', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS,MAXDOP=0;
--COMMIT;
GO
ALTER DATABASE test_db
SET MULTI_USER;
GO
If that doesn't work either and we have corruption in the clustered index, the only thing we can do is restore the base with the latest backup we have.
