In this post, I am going to provide basic information to monitor and manage Database Corruption of SQL Server.
As a Database Administrator, this is our responsibility to find corruption of the database before database crash happened.
You can monitor corrupted database page using one system table of MSDB.
Please execute this 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?
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 happened 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.
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.