{"id":575,"date":"2016-08-08T15:12:32","date_gmt":"2016-08-08T20:12:32","guid":{"rendered":"http:\/\/www.sqlfreelancer.com\/blog\/?p=575"},"modified":"2016-08-08T15:12:32","modified_gmt":"2016-08-08T20:12:32","slug":"sql-server-dbcc-checkdb-overview","status":"publish","type":"post","link":"https:\/\/www.sqlfreelancer.com\/blog\/sql-server-dbcc-checkdb-overview\/","title":{"rendered":"SQL Server DBCC CHECKDB Overview"},"content":{"rendered":"<p>SQL Server database corruption can be a problem and can cause serious damage to a database. If you\u2019re an experienced DBA then you probably have safeguards in place to detect this, but over the years I\u2019ve seen hundreds of SQL Servers with no detection methods at all and this is a problem. There are a few ways to detect database corruption, but this tip will focus more on DBCC CHECKDB.<\/p>\n<p>You may or may not have heard of DBCC (database console commands) statements. These statements are used to perform different operations in your database and can be broken down into four categories: Maintenance, Miscellaneous, Informational, and Validation. I use some of the DBCC statements on a daily basis, but none more than DBCC CHECKDB.<\/p>\n<h2><b>What is SQL Server DBCC CHECKDB<\/b><\/h2>\n<p>DBCC CHECKDB, from <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms176064.aspx\" target=\"_blank\"> Microsoft MSDN Library<\/a>, checks logical and physical integrity of all the objects in the specified database by performing the following operations:<\/p>\n<ul>\n<li>Runs DBCC CHECKALLOC on the database \u2013 Checks consistency of disk space allocation structures for a specified database.<\/li>\n<li>Runs DBCC CHECKTABLE on every table and view in the database \u2013 Checks the integrity of all the pages and structures that make up the table or indexed view.<\/li>\n<li>Runs DBCC CHECKCATALOG on the database \u2013 Checks for catalog consistency within the database.<\/li>\n<li>Validates the contents of every indexed view in the database.<\/li>\n<li>Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.<\/li>\n<li>Validates the Service Broker data in the database<\/li>\n<\/ul>\n<p>If you\u2019ve ever ran DBCC CHECKDB you know it takes some time for large databases. Now that you know all of the steps that are run, you can see why it takes time to complete.<\/p>\n<h2><b>How can SQL Server DBCC CHECKDB help me?<\/b><\/h2>\n<p>Data corruption is bad. It can cause all sorts of issues within the database that may include incorrect data results, failed SQL statements, and in some cases can take down the entire SQL instance. DBCC CHECKDB warns you of corruption so that you can fix it before (hopefully) it gets too bad.<\/p>\n<h2><b>How do I use SQL Server DBCC CHECKDB?<\/b><\/h2>\n<p>DBCC CHECKDB is pretty straightforward. There are a few options you can use with the statement and we\u2019ll go over some of those in the next section, but the basic syntax looks like this:<\/p>\n<pre>DBCC CHECKDB ('DatabaseName')<\/pre>\n<p>Pretty simple.<\/p>\n<h2>Automate SQL Server DBCC CHECKDB<\/h2>\n<p>Obviously, you don\u2019t want to log in every morning and run this statement on each database, so you can automate this process using a few different methods:<\/p>\n<ul>\n<li><strong>SQL Server Maintenance plans<\/strong> \u2013 Maintenance plans are part of SQL Server out of the box (unless you\u2019re running Express Edition). I don\u2019t like using maintenance plans for the most part, but I don\u2019t mind using them for this type of task. In the Maintenance Plan toolbox you\u2019ll need to use the Check Database Integrity task. The only configurable option is to include indexes so it\u2019s not really user friendly, but in some cases this is all you need. Again, we\u2019ll talk about other options in the next section.<\/li>\n<\/ul>\n<p><img decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/www.mssqltips.com\/tipimages2\/4381_CHECKDB.png\" \/><\/p>\n<p><strong>Custom scripts<\/strong> \u2013 Custom scripts are usually what I use and offer the best flexibility as far as adding the options you want. My go-to scripts are already created and free to use from <a href=\"https:\/\/ola.hallengren.com\/\" target=\"_blank\">Ola Hallengren<\/a>. He\u2019s done a wonderful job of creating these and sharing them to the world. Thanks Ola!<\/p>\n<p><a href=\"https:\/\/www.mssqltips.com\/sqlservertip\/4381\/sql-server-dbcc-checkdb-overview\/\">Click here to view the rest of this post.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQL Server database corruption can be a problem and can cause serious damage to a database. If you\u2019re an experienced DBA then you probably have safeguards in place to detect this, but over the years I\u2019ve seen hundreds of SQL Servers with no detection methods at all and this is a problem. There are a [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[91],"tags":[79,117],"class_list":["post-575","post","type-post","status-publish","format-standard","hentry","category-maintenance","tag-dbcc","tag-maintenance"],"_links":{"self":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/575","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/comments?post=575"}],"version-history":[{"count":4,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/575\/revisions"}],"predecessor-version":[{"id":579,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/575\/revisions\/579"}],"wp:attachment":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/media?parent=575"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/categories?post=575"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/tags?post=575"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}