2

SQL SERVER - Quick Look at Suspected Pages - SQL Authority with Pinal Dave

 3 years ago
source link: https://blog.sqlauthority.com/2021/07/12/sql-server-quick-look-at-suspected-pages/?utm_campaign=sql-server-quick-look-at-suspected-pages
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

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.

SQL SERVER - Quick Look at Suspected Pages suspectedpages-800x196

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.

Reference: Pinal Dave (http://blog.SQLAuthority.com)


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK