One of my Comprehensive Database Performance Health Check clients asked me if I can help them know if they have any corruption in the system. They have been running DBCC commands regularly on their server but never spent time looking deeper into the messages. Well, let us take a Quick Look at Suspected Pages which contains details about corrupted pages.
Here is the script which you can run to check the details about the corrupted pages.
SELECT db.name AS DatabaseName, sp.page_id AS PageID, sp.event_type EventType, sp.error_count AS ErrorCount, sp.last_update_date AS LastUpdated, mf.name as LogicalName, mf.physical_name as FilePath FROM msdb.dbo.suspect_pages AS sp INNER JOIN sys.databases AS db ON db.database_id = sp.database_id INNER JOIN sys.master_files AS mf ON mf.database_id = sp.database_id AND mf.file_id = sp.file_id
When you run the script above it will give you the name of the database with logical and physical file name along with the error count and event type. You can use this information to find your error details from suspected pages.
Regarding event type, here are the quick details about error types its description.
- Error Type 1 – 823 error caused by an operating system CRC error or 824 error other than a bad checksum or a torn page (for example, a bad page ID)
- Error Type 2 – Bad checksum
- Error Type 3 – Torn page
- Error Type 4 – Restored (The page was restored after it was marked bad)
- Error Type 5 – Repaired (DBCC repaired the page)
- Error Type 7 – Deallocated by DBCC
If you liked this blog, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.
Here are my few recent videos and I would like to know what is your feedback about them.
- One Scan for 3 Count Sum – SQL in Sixty Seconds #178
- SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
- COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
- COUNT(*) and Index – SQL in Sixty Seconds #175
- Index Scans – Good or Bad? – SQL in Sixty Seconds #174
- Optimize for Ad Hoc Workloads – SQL in Sixty Seconds #173
- Avoid Join Hints – SQL in Sixty Seconds #172
- One Query Many Plans – SQL in Sixty Seconds #171
Reference: Pinal Dave (http://blog.SQLAuthority.com)