This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am going to provide necessary 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 about corruption.
1 2 |
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, execute DBCC CHECKDB.
1 |
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.