In this post, I am going to provide basic information to monitor and manage Database Corruption of SQL Server.
As we are Database Administrator, so this is our responsibility to find data corruption of the database before it crashes.
You can monitor corrupted database page using one system table of MSDB.
Please execute below statement to find information of corruption.
SELECT * FROM msdb.dbo.suspect_pages
WHERE (event_type = 1 OR event_type = 2 OR event_type = 3);
This corrupted page logged in different situations.
- 824 error : Torn pages error.
- 823 error: Disk or Hardware error.
- DBCC repaired the page.
- Bad checksum.
- DBCC deallocation.
- Restored Database with Bad marked.
How to deal with this corrupted pages?
The first step, I always suggest is that, execute DBCC CHECKDB.
DBCC CHECKDB () WITH NO_INFOMSGS, ALL_ERRORMSGS
You should check your database backup strategy and recovery model.
If corruption happens and not resolved yet, you can restore a fresh backup.
Another best practice is to take the full database object script for Database structure.
You can also use any other third-party recovery tools.