SQL SERVER – Quick Look at Suspected Pages

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)