{"id":51556,"date":"2022-07-15T16:55:31","date_gmt":"2022-07-15T16:55:31","guid":{"rendered":"https:\/\/dbtut.com\/?p=51556"},"modified":"2022-07-15T16:57:24","modified_gmt":"2022-07-15T16:57:24","slug":"sql-server-suspect-pages","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/","title":{"rendered":"SQL Server Suspect Pages"},"content":{"rendered":"

In today’s article, we will talk about how to detect SQL Server Suspect Pages.<\/p>\n

I will explain how you can detect database corruption using the suspect_pages table in the msdb database.<\/p>\n

SQL Server announced in 2005 that the SUSPECT_PAGES table is available in the msdb system database. Since SQL Server 2005, every corrupted page information is added to the msdb.dbo.suspect_pages table.<\/p>\n

Due to SQL Server database structure, if it encounters one of the following errors, it is considered corrupted. (According to the information on MSDN)<\/p>\n

Error 823 occurs when a CRC (Cyclic Redundancy Check) error occurs by the operating system, such as disk failure or some hardware failure. A Windows read or write job has failed.<\/p>\n

Torn Page Logical Consistency \u2013 824 error \u2013 This error occurs because windows is reading the page successfully but the sql server is experiencing an inconsistency on the page. It has an infrastructure similar to the 823 error.<\/p>\n

When will a record arrive in the SUSPECT_PAGES table?<\/h5>\n

The SQL Server database engine provides logging of page corruptions encountered during any of the following operations.<\/p>\n

During the execution of the DBCC CHECKDB command<\/p>\n

During Database Backup and Restore<\/p>\n

During a corrupted page read of the query being run<\/p>\n

During DBCC CHECKDB REPAIR_ALLAW_DATA_LOSS operation<\/p>\n

While deleting database \u2013 Drop Database operations<\/p>\n

Important note! :<\/h5>\n

We should run the DBCC CHECKDB command periodically during maintenance.<\/p>\n

What are the errors recorded in the SUSPECT_PAGES table?<\/h5>\n

The following table contains the descriptions of the errors according to the event_type data.<\/p>\n\n\n\n\n\n\n\n\n\n
Event_type<\/td>\nError Description<\/td>\n<\/tr>\n
1<\/td>\nError records 823 and 824 at the operating system level \u2013 CRC Error<\/td>\n<\/tr>\n
2<\/td>\nBad CheckSum<\/td>\n<\/tr>\n
3<\/td>\nTorn Page<\/td>\n<\/tr>\n
4<\/td>\nRestored (The page was restored after it was marked bad) \u2013The page was broken, but it has been repaired.<\/td>\n<\/tr>\n
5<\/td>\nRepaired with DBCC<\/td>\n<\/tr>\n
7<\/td>\nDeallocated by DBCC<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

We can find corrupted pages using the following query.<\/p>\n

SELECT\r\nSD.name AS DatabaseName\r\n,MSP.file_id AS FileID\r\n,SMF.physical_name AS PhysicalFilePath\r\n,MSP.page_id AS PageID\r\n,CASE\r\nWHEN MSP.event_type = 1 THEN \u2018823 error caused by an operating system CRC error or 824 error other than a bad checksum or a torn page\u2019\r\nWHEN MSP.event_type = 2 THEN \u2018Bad checksum\u2019\r\nWHEN MSP.event_type = 3 THEN \u2018Torn Page\u2019\r\nWHEN MSP.event_type = 4 THEN \u2018Restored (The page was restored after it was marked bad)\u2019\r\nWHEN MSP.event_type = 5 THEN \u2018Repaired (DBCC repaired the page)\u2019\r\nWHEN MSP.event_type = 7 THEN \u2018Deallocated by DBCC\u2019\r\nEND AS EventDescription\r\n,MSP.error_count AS ErrorCount\r\n,MSP.last_update_date AS LastUpdated\r\nFROM msdb.dbo.suspect_pages MSP\r\nINNER JOIN sys.databases SD ON SD.database_id = MSP.database_id\r\nINNER JOIN sys.master_files SMF ON SMF.database_id = MSP.database_id AND SMF.file_id = MSP.file_id<\/pre>\n
Maintaining the SUSPECT_PAGES table<\/p>\n
Database administrators should periodically review the SUSPECT_PAGES table and delete old rows as this table is limited to 1000 rows.<\/p>\n
If the table is full, then new errors will not be recorded.<\/p>\n
In order to delete data from the SUSPECT_PAGES Table, you must be logged into SQL Server with a user with db_owner authority or sysadmin authority from the msdb database.<\/p>\n
DELETE FROM msdb..suspect_pages\r\nWHERE (event_type = 4\r\nOR event_type = 5\r\nOR event_type = 7)<\/pre>\n
 <\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
In today’s article, we will talk about how to detect SQL Server Suspect Pages. I will explain how you can detect database corruption using the suspect_pages table in the msdb database. SQL Server announced in 2005 that the SUSPECT_PAGES table is available in the msdb system database. Since SQL Server 2005, every corrupted page information …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1414,"featured_media":51557,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[4],"tags":[],"class_list":["post-51556","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":113,"today_views":0},"yoast_head":"\nSQL Server Suspect Pages - Database Tutorials<\/title>\n<meta name=\"description\" content=\"In today's article, we will talk about how to detect SQL Server Suspect Pages.I will explain how you can detect database corruption using\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Suspect Pages - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"In today's article, we will talk about how to detect SQL Server Suspect Pages.I will explain how you can detect database corruption using\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2022-07-15T16:55:31+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-07-15T16:57:24+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2022\/07\/Ekran-goruntusu-2022-07-15-195301.png\" \/>\n\t<meta property=\"og:image:width\" content=\"783\" \/>\n\t<meta property=\"og:image:height\" content=\"396\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"\u00c7a\u011flar \u00d6zen\u00e7\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"\u00c7a\u011flar \u00d6zen\u00e7\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/\"},\"author\":{\"name\":\"\u00c7a\u011flar \u00d6zen\u00e7\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/92baa6fd666fb707d903177fed07d6ab\"},\"headline\":\"SQL Server Suspect Pages\",\"datePublished\":\"2022-07-15T16:55:31+00:00\",\"dateModified\":\"2022-07-15T16:57:24+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/\"},\"wordCount\":378,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2022\/07\/Ekran-goruntusu-2022-07-15-195301.png\",\"articleSection\":[\"ORACLE\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/\",\"name\":\"SQL Server Suspect Pages - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2022\/07\/Ekran-goruntusu-2022-07-15-195301.png\",\"datePublished\":\"2022-07-15T16:55:31+00:00\",\"dateModified\":\"2022-07-15T16:57:24+00:00\",\"description\":\"In today's article, we will talk about how to detect SQL Server Suspect Pages.I will explain how you can detect database corruption using\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2022\/07\/Ekran-goruntusu-2022-07-15-195301.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2022\/07\/Ekran-goruntusu-2022-07-15-195301.png\",\"width\":783,\"height\":396},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Suspect Pages\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/92baa6fd666fb707d903177fed07d6ab\",\"name\":\"\u00c7a\u011flar \u00d6zen\u00e7\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/997658bc236de4f5a0f3f46e64535566e31ba96824c77c01165e863fc38fd1ba?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/997658bc236de4f5a0f3f46e64535566e31ba96824c77c01165e863fc38fd1ba?s=96&d=mm&r=g\",\"caption\":\"\u00c7a\u011flar \u00d6zen\u00e7\"},\"url\":\"https:\/\/dbtut.com\/index.php\/author\/caglarozenc\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Suspect Pages - Database Tutorials","description":"In today's article, we will talk about how to detect SQL Server Suspect Pages.I will explain how you can detect database corruption using","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Suspect Pages - Database Tutorials","og_description":"In today's article, we will talk about how to detect SQL Server Suspect Pages.I will explain how you can detect database corruption using","og_url":"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/","og_site_name":"Database Tutorials","article_published_time":"2022-07-15T16:55:31+00:00","article_modified_time":"2022-07-15T16:57:24+00:00","og_image":[{"width":783,"height":396,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2022\/07\/Ekran-goruntusu-2022-07-15-195301.png","type":"image\/png"}],"author":"\u00c7a\u011flar \u00d6zen\u00e7","twitter_card":"summary_large_image","twitter_misc":{"Written by":"\u00c7a\u011flar \u00d6zen\u00e7","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/"},"author":{"name":"\u00c7a\u011flar \u00d6zen\u00e7","@id":"https:\/\/dbtut.com\/#\/schema\/person\/92baa6fd666fb707d903177fed07d6ab"},"headline":"SQL Server Suspect Pages","datePublished":"2022-07-15T16:55:31+00:00","dateModified":"2022-07-15T16:57:24+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/"},"wordCount":378,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2022\/07\/Ekran-goruntusu-2022-07-15-195301.png","articleSection":["ORACLE"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/","url":"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/","name":"SQL Server Suspect Pages - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2022\/07\/Ekran-goruntusu-2022-07-15-195301.png","datePublished":"2022-07-15T16:55:31+00:00","dateModified":"2022-07-15T16:57:24+00:00","description":"In today's article, we will talk about how to detect SQL Server Suspect Pages.I will explain how you can detect database corruption using","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2022\/07\/Ekran-goruntusu-2022-07-15-195301.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2022\/07\/Ekran-goruntusu-2022-07-15-195301.png","width":783,"height":396},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2022\/07\/15\/sql-server-suspect-pages\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"SQL Server Suspect Pages"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/92baa6fd666fb707d903177fed07d6ab","name":"\u00c7a\u011flar \u00d6zen\u00e7","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/997658bc236de4f5a0f3f46e64535566e31ba96824c77c01165e863fc38fd1ba?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/997658bc236de4f5a0f3f46e64535566e31ba96824c77c01165e863fc38fd1ba?s=96&d=mm&r=g","caption":"\u00c7a\u011flar \u00d6zen\u00e7"},"url":"https:\/\/dbtut.com\/index.php\/author\/caglarozenc\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/51556","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1414"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=51556"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/51556\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/51557"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=51556"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=51556"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=51556"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}