[Note from Pinal]: This is a new episode of Notes from the Fields series. I often hear about database corruption and I like to stay away from the problems related to corruption. When database gets corrupted, there are not enough expert out in the world who can fix it properly to its original state. John who is my good friend explains a very interesting situation in this blog post where we can learn about corruption with the help of suspect pages table. It is indeed a great tip for everyone to learn even though your database is not corrupted.
In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very common issue DBAs and Developer faces related to Detecting Corruption with Suspect Pages Table. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.
When I get asked to review a database server instance, a very important check that I perform is to review if corruption has occurred with the existing databases. In today’s tip from the field we are going to go over a simple query that will identify existing corrupted pages without running DBCC CHECKDB.
In SQL Server you will not be notified of corruption until an attempt to read a corrupt page occurs. Therefore a page could have been corrupted weeks ago and no one would have noticed if the page was never accessed since the corruption occurred. We strongly recommend using an automated schedule to check database integrity by running DBCC CHECKDB.
DBCC CHECKDB is a very I/O intensive operation so you shouldn’t run the command at will. It should be executed during defined maintenance windows. Today, you are going to see that we can track down corrupt pages with the query below.
SELECT * FROM msdb.dbo.suspect_pages
Ideally, the result set will return zero rows. Zero rows means that no pages have been identified as being corrupt. It doesn’t guarantee that you don’t have corrupt pages. It just means no corrupt pages have been accessed. If you have results then you just identified pages within database files that have a history of corruption. The error count will let you know how many times the page(s) have been accessed since it was added to the suspect pages table. The event type column will identify the type of corruption.
NOTE: The suspect_pages table in msdb database will only keep 1,000 pages. Hopefully you will not have any rows yet alone more than 1,000 corrupt pages. If you do have more than 1,000 than you would only see the last 1,000 pages in this table.
Keep in mind that the suspect_pages table will only include marked corrupted pages. Corrupt pages are only classified as marked if they have been accessed since the corruption occurred. This is why you need to include DBCC CHECKDB in your maintenance plans.
CALL TO ACTION:
- Be proactive and monitor suspect_pages table in msdb database
- Implement DBCC CHECKDB in your maintenance plans.
Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
