{"id":6614,"date":"2022-07-19T05:37:26","date_gmt":"2022-07-19T09:37:26","guid":{"rendered":"https:\/\/www.filerepairtools.com\/tips\/?p=6614"},"modified":"2022-07-19T05:37:26","modified_gmt":"2022-07-19T09:37:26","slug":"how-to-check-database-corruption-in-sql-server","status":"publish","type":"post","link":"https:\/\/filerepairtools.com\/tips\/how-to-check-database-corruption-in-sql-server\/","title":{"rendered":"How to Check Database Corruption in SQL Server?"},"content":{"rendered":"\n<p><strong>Introduction<\/strong><\/p><div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_1 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/filerepairtools.com\/tips\/how-to-check-database-corruption-in-sql-server\/#Common_causes_of_SQL_Database_corruption\" >Common causes of SQL Database corruption<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/filerepairtools.com\/tips\/how-to-check-database-corruption-in-sql-server\/#Method-1_Run_the_DBCC_CHECKDB_command\" >Method-1. Run the DBCC CHECKDB command.<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/filerepairtools.com\/tips\/how-to-check-database-corruption-in-sql-server\/#Method-2_Set_Page_Verify_option_with_CHECKSUM\" >Method-2. Set Page Verify option with CHECKSUM.<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/filerepairtools.com\/tips\/how-to-check-database-corruption-in-sql-server\/#Method-3_Backup_using_CHECKSUM\" >Method-3. Backup using CHECKSUM.<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/filerepairtools.com\/tips\/how-to-check-database-corruption-in-sql-server\/#Method-4_Set_the_alerts\" >Method-4. Set the alerts.<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/filerepairtools.com\/tips\/how-to-check-database-corruption-in-sql-server\/#Method-5_Recover_database_using_a_professional_solution\" >Method-5. Recover database using a professional solution.<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/filerepairtools.com\/tips\/how-to-check-database-corruption-in-sql-server\/#Conclusion\" >Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n\n\n\n\n<p>The majority of SQL database users are not aware of the structure that how a database works. They save their data in it and retrieve it whenever required. That\u2019s why when the SQL Server shows an error, then they cannot recover the tables saved in the database. A SQL database contains several objects like tables, indexes, triggers, procedures, and views. The logical collection of all these objects is called a SQL schema and corruption in any object may disrupt such schema.<\/p>\n\n\n\n<p>The main job responsibility of the Database Administrator is to protect the schema from various attacks and check for the database\u2019s health routinely.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Common_causes_of_SQL_Database_corruption\"><\/span><strong>Common causes of SQL Database corruption<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>SQL creates the database in two formats \u2013 MDF (Master Database File) and NDF (Not Master Database File). There can be a variety of reasons that affect both of these databases and show multiple errors as presented below-<\/p>\n\n\n\n<p><strong><em><a href=\"https:\/\/www.filerepairtools.com\/tips\/resolve-sql-error-5172-syntax-error-1064\/\" data-type=\"post\" data-id=\"1122\">SQL Error \u2013 5172<\/a>.<\/em><\/strong><\/p>\n\n\n\n<p>\u201cThe header for file \u2018DatabaseName\u2019 is not a valid database file header. The FILE SIZE property is incorrect.\u201d<\/p>\n\n\n\n<p><strong><em>SQL Fatal Error \u2013 823.<\/em><\/strong><\/p>\n\n\n\n<p>\u201cThe Operating System returned error 117 to SQL Server at offset.\u201d<\/p>\n\n\n\n<p><strong><em>SQL Error \u2013 8946.<\/em><\/strong><\/p>\n\n\n\n<p>\u201cAllocation Page (Page ID) has invalid PAGE_TYPE page header values.\u201d<\/p>\n\n\n\n<p>These are some common SQL server errors that show the problems directly related to the bad schema or corruption in the database. Some common reasons behind such errors are-<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Abrupt Shutdown of Server.<\/li><li>Abrupt shut down of the user computer.<\/li><li>Presence of bugs in outdated SQL Server.<\/li><li>Unexpected changes in the SQL account.<\/li><li>Corruption in the File header.<\/li><li>Malware corruption.<\/li><li>Ransomware attack.<\/li><\/ul>\n\n\n\n<p>Generally, SQL Server shows such above-mentioned errors whenever it detects some bad elements in the system, but many times it does not show some visible error messages. The Administrator has to check for the integrity of the database (both physical and logical) of the entire dataset. There are various methods to check for database corruption in SQL Server and you can examine them based on the behavior of SQL Server.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Method-1_Run_the_DBCC_CHECKDB_command\"><\/span><strong>Method-1. Run the DBCC CHECKDB command.<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The CHECKDB command is the first step in starting the database repair process as it brings out the irregularities in the database schema.<\/p>\n\n\n\n<p><em>DBCC CHECKDB &lt;Database Name&gt;<\/em><\/p>\n\n\n\n<p>It will run a scan in the selected database and bring out a detailed description of all the errors, issues, and corruption in the affected object.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Method-2_Set_Page_Verify_option_with_CHECKSUM\"><\/span><strong>Method-2. Set Page Verify option with CHECKSUM.<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The Page Verify option present in the database properties shows the health of a selected page during each write\/read action. It gives a deep control on keeping the information healthy in the database.<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\"><li>In SQL Server Management Studio, go to the database and choose its properties after right-clicking.<\/li><li>In the Options category, there is a feature \u2013 Recovery. In the Page Verify option \u2013 there is a dropdown list where you can see three options \u2013 CHECKSUM, TORN_PAGE_DETECTION, and NONE. Set the option to CHECKSUM. Click OK.<\/li><\/ol>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.filerepairtools.com\/tips\/wp-content\/uploads\/2022\/07\/check-database-corruption-sql-server.jpg\"><img decoding=\"async\" src=\"https:\/\/www.filerepairtools.com\/tips\/wp-content\/uploads\/2022\/07\/check-database-corruption-sql-server.jpg\" alt=\"check database corruption in sql server\" class=\"wp-image-6615\"\/><\/a><\/figure>\n\n\n\n<p>The CHECKSUM will check the entire page before it is written to the database.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Method-3_Backup_using_CHECKSUM\"><\/span><strong>Method-3. Backup using CHECKSUM.<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>When you trying to take a complete backup of a database and you use the CHECKSUM feature, then it will make sure that you are saving a healthy database backup available for restoration.<\/p>\n\n\n\n<p><em>BACKUP DATABASE &lt;Database Name&gt;<\/em><\/p>\n\n\n\n<p><em>&nbsp;&nbsp; TO DISK=disk path&#8217;<\/em><\/p>\n\n\n\n<p><em>&nbsp;&nbsp; WITH CHECKSUM;<\/em><\/p>\n\n\n\n<p>The backup will create a BAK file consisting of complete database objects in it.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Method-4_Set_the_alerts\"><\/span><strong>Method-4. Set the alerts.<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>If the DBA gets an instant notification when corruption is occurring in the database, then it will be faster to recognize the causes and rectify the problem immediately.<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\"><li>Open the Performance window and click expand the option to open Performance Logs and Alerts.<\/li><li>Click on the New Alert Settings option after right-clicking on Alerts.<\/li><li>Provide a name to the alert and click OK.<\/li><li>Go to the General tab and choose to Add option to add a counter.<\/li><li>Choose a SQL Server object from the Performance Object list and select a counter from the available list.<\/li><li>Click Add to select the counter.<\/li><li>In the Alert dialog box, choose either the Under or Over option for \u2018Alert when the value is\u2019 list. It is the limit for the occurrence of alert jobs.<\/li><li>On the Sample Data Every option, input the sampling frequency.<\/li><li>Choose the action that will trigger when the alert occurs. Set it into the Action tab.<\/li><li>On the schedule tab, put the start and stop timings for running the alerts.<\/li><\/ol>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Method-5_Recover_database_using_a_professional_solution\"><\/span><strong>Method-5. Recover database using a professional solution.<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>When the database shows some errors in DBDC CHECKDB and you had not taken a backup of the affected object, then it will be deleted completely. So, the final method to repair and recover all the SQL Database objects will be using a professional SQL Database Repair software that can scan the MDF\/NDF files and recover all data.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span><strong>Conclusion<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h1>\n\n\n\n<p>SQL Server is the backbone where businesses save their professional information related to employees, clients, vendors, inventory, etc. A disruption in the database is bad news for the manager and you cannot afford to lose it. So, use a professional SQL Database software and be ready to overcome any of the corruption or deletion problems in SQL Server.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction The majority of SQL database users are not aware of the structure that how a database works. They save their data in it and retrieve it whenever required. That\u2019s why when the SQL Server shows an error, then they cannot recover the tables saved in the database. A SQL database contains several objects like [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3],"tags":[],"class_list":["post-6614","post","type-post","status-publish","format-standard","hentry","category-database-repair"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/filerepairtools.com\/tips\/wp-json\/wp\/v2\/posts\/6614","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/filerepairtools.com\/tips\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/filerepairtools.com\/tips\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/filerepairtools.com\/tips\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/filerepairtools.com\/tips\/wp-json\/wp\/v2\/comments?post=6614"}],"version-history":[{"count":0,"href":"https:\/\/filerepairtools.com\/tips\/wp-json\/wp\/v2\/posts\/6614\/revisions"}],"wp:attachment":[{"href":"https:\/\/filerepairtools.com\/tips\/wp-json\/wp\/v2\/media?parent=6614"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/filerepairtools.com\/tips\/wp-json\/wp\/v2\/categories?post=6614"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/filerepairtools.com\/tips\/wp-json\/wp\/v2\/tags?post=6614"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}