{"id":401,"date":"2018-01-26T12:03:12","date_gmt":"2018-01-26T18:03:12","guid":{"rendered":"http:\/\/www.sqlkitten.com\/?p=401"},"modified":"2018-01-26T12:11:50","modified_gmt":"2018-01-26T18:11:50","slug":"last-known-good-checkdb-for-all-your-databases","status":"publish","type":"post","link":"http:\/\/www.sqlkitten.com\/?p=401","title":{"rendered":"Last known good CHECKDB&#8230;for ALL your databases!"},"content":{"rendered":"<p><del>This morning<\/del> Wednesday I walk into the office and immediately hear that CHECKDB is the source of issues on one of the servers and is the reason behind some errors that have been happening. While I don&#8217;t think this is the case (it might look like it on the surface but there is something else that is happening that is the actual cause) I also wanted to find out what CHECKDB was running at the time the errors occurred.<\/p>\n<p>I needed information on when CHECKDB ran for each database. When you look for what you can run to find when CHECKDB was last run you find <a href=\"http:\/\/jasonbrimhall.info\/2012\/11\/20\/last-known-good-checkdb\/\">this blog post<\/a> and also <a href=\"https:\/\/www.brentozar.com\/archive\/2015\/08\/getting-the-last-good-dbcc-checkdb-date\/\">this blog post<\/a> on grabbing this info. While these were very informative, they were for one database at a time. I need this for all the databases so I can try to not only find out when each one ran, but also use these time stamps to figure out the duration.<\/p>\n<p>I took the code from Jason&#8217;s post and made a few changes, running this for all the database with sp_MSForEachDB.<\/p>\n<pre class=\"lang:tsql decode:true\">-----create the table - added dbname------\r\nCREATE TABLE #temp (Id INT IDENTITY(1,1)\r\n,ParentObject VARCHAR(255)\r\n,[Object] VARCHAR(255)\r\n,Field VARCHAR(255)\r\n,[Value] VARCHAR(255)\r\n,dbname varchar(50)\r\n)\r\n\r\n-----using sp_MSforeachdb, grab the data for each database and update the dbname field--------\r\nexec sp_MSforeachdb &#039;INSERT INTO #temp(ParentObject, [Object], Field, [Value])\r\nEXECUTE (&#039;&#039;DBCC Page ( ?,1,9,3) WITH TABLERESULTS&#039;&#039;);\r\n\r\nupdate #temp\r\nset dbname = &#039;&#039;?&#039;&#039;\r\nwhere dbname is null&#039;\r\n\r\n-----select from the table and BEHOLD THE AWESOMENESS!---------- \r\nSELECT *\r\nFROM #temp\r\nWHERE Field = &#039;dbi_dbccLastKnownGood&#039;\r\norder by 5\r\n\r\n-----drop that table like a bad habit----- \r\nDROP TABLE #temp;\r\nGO<\/pre>\n<p>The results gave me some good information to go on &#8211; the database where the errors occurred was not the same database where CHECKDB was running during that time frame. There were also some FlushCache messages in the log &#8211; some suspect that this is also tied to the errors that are happening. Since all this ties back to NHibernate code, I suspect something else is going on and further digging is needed.<\/p>\n<p><a href=\"http:\/\/www.sqlkitten.com\/wp-content\/uploads\/2018\/01\/dbcc_checkdb2.png\"><img loading=\"lazy\" class=\"aligncenter size-full wp-image-402\" src=\"http:\/\/www.sqlkitten.com\/wp-content\/uploads\/2018\/01\/dbcc_checkdb2.png\" alt=\"\" width=\"766\" height=\"403\" srcset=\"http:\/\/www.sqlkitten.com\/wp-content\/uploads\/2018\/01\/dbcc_checkdb2.png 766w, http:\/\/www.sqlkitten.com\/wp-content\/uploads\/2018\/01\/dbcc_checkdb2-300x158.png 300w\" sizes=\"(max-width: 766px) 100vw, 766px\" \/><\/a><\/p>\n<p>Situation developing. \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This morning Wednesday I walk into the office and immediately hear that CHECKDB is the source of issues on one of the servers and is the reason behind some errors that have been happening. While I don&#8217;t think this is &hellip; <a href=\"http:\/\/www.sqlkitten.com\/?p=401\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=\/wp\/v2\/posts\/401"}],"collection":[{"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=401"}],"version-history":[{"count":7,"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=\/wp\/v2\/posts\/401\/revisions"}],"predecessor-version":[{"id":409,"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=\/wp\/v2\/posts\/401\/revisions\/409"}],"wp:attachment":[{"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=401"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=401"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=401"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}