SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Checking CHECKDB


Checking CHECKDB

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)

Group: Administrators
Points: 531931 Visits: 20696
Comments posted to this topic are about the item Checking CHECKDB

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Andy Robertson
Andy Robertson
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2285 Visits: 438

Hi Steve, nice article, thanks. The Paul S. Randal article you linked to was also very interesting. What sort of frequency would you run CHECKDB on your databases? Would this be a weekly thing? Do you run it with no arguments. I'm just curious as to the typical usage. I notice it hasn't run for a while on some of our production DBs which makes me a little concerned!
I'm guessing this is the sort of thing that you schedule to run overnight or overnight on the weekend when the impact would be reduced?


Rick-153145
Rick-153145
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1198 Visits: 618
Andy, it depends on the size of your databases, your sla's etc, but I would suggest at least once a week during a quiet time.

One thing I keep coming across where I am currently working is DBCC errors due to spatial indexes with different versions of SQL. Its a pain, but the environments here are all over the place (I am trying to standardise everything), this does give me a good excuse to suggest upgrades though.
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95138 Visits: 13840
.. The problem is that for many of us, we may run DBCC CHECKDB on another machine, perhaps on a restored copy of production, so how can we track this? ..


The default trace logs DBCC CHECKDB events.



"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Jeff Mlakar
Jeff Mlakar
SSC Eights!
SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)

Group: General Forum Members
Points: 886 Visits: 369
I like to check by:
DBCC DBINFO ('MyDbName') WITH TABLERESULTS

Search the field column in the output for dbo_dbccLastKnownGood for the datetime stamp value.
jasona.work
jasona.work
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38034 Visits: 15406
Eric M Russell - Tuesday, May 22, 2018 7:24 AM
.. The problem is that for many of us, we may run DBCC CHECKDB on another machine, perhaps on a restored copy of production, so how can we track this? ..


The default trace logs DBCC CHECKDB events.

It does, true, but the question raised in the article is, if you run CHECKDB against a copy, how does someone else know you ran it against that DB if they're checking production? How do you somehow transfer the results from the copy to production?

If you're on SQL2016 SP1, you can't update the property (DatabasePropertyEx() -> LastGoodCheckDbTime) so how would you do it?
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95138 Visits: 13840
jasona.work - Tuesday, May 22, 2018 1:15 PM
Eric M Russell - Tuesday, May 22, 2018 7:24 AM
.. The problem is that for many of us, we may run DBCC CHECKDB on another machine, perhaps on a restored copy of production, so how can we track this? ..


The default trace logs DBCC CHECKDB events.

It does, true, but the question raised in the article is, if you run CHECKDB against a copy, how does someone else know you ran it against that DB if they're checking production? How do you somehow transfer the results from the copy to production?

If you're on SQL2016 SP1, you can't update the property (DatabasePropertyEx() -> LastGoodCheckDbTime) so how would you do it?

I don't see the point in transferring results. The online database and the restored copy are two different instances. Just because one is checks out ok, the other could still be bad.



"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Steve Jones
Steve Jones
SSC Guru
SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)

Group: Administrators
Points: 531931 Visits: 20696
allinadazework - Tuesday, May 22, 2018 2:06 AM

Hi Steve, nice article, thanks. What sort of frequency would you run CHECKDB on your databases? Would this be a weekly thing? Do you run it with no arguments. I'm just curious as to the typical usage.



Ideally I run it everyday on every database I care about. However, that depends on size/resources. I have run it weekly on restored copies of databases because it just took too long and impacted performance. As a result, we had a couple spare servers (old desktops) that we'd added storage to that were dedicated to taking a backup, restoring it, running dbcc, saving results, drop the db, repeat with a new db. In this way, we had a couple systems checking dozens of databases.

I've tended to just run dbcc checkdb by itself.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Steve Jones
Steve Jones
SSC Guru
SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)SSC Guru (531K reputation)

Group: Administrators
Points: 531931 Visits: 20696
Eric M Russell - Tuesday, May 22, 2018 2:12 PM

I don't see the point in transferring results. The online database and the restored copy are two different instances. Just because one is checks out ok, the other could still be bad.


Potentially, but a backup of the database is a valid way to run checkdb, especially when you can't spare resources on the online database. Since in this case, many times people use a dedicated server to check many databases, the restored copy isn't maintained. It's dropped. Capturing that information would be helpful for monitoring to know if dbcc has been run for a particular database.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95138 Visits: 13840
Steve Jones - SSC Editor - Wednesday, May 23, 2018 8:54 AM
Eric M Russell - Tuesday, May 22, 2018 2:12 PM

I don't see the point in transferring results. The online database and the restored copy are two different instances. Just because one is checks out ok, the other could still be bad.


Potentially, but a backup of the database is a valid way to run checkdb, especially when you can't spare resources on the online database. Since in this case, many times people use a dedicated server to check many databases, the restored copy isn't maintained. It's dropped. Capturing that information would be helpful for monitoring to know if dbcc has been run for a particular database.

It's good practice to test our backups, so it would make sense to automate the process of: backup from server A, restore to server B, run checkdb on server B, and then also keep server B online to miscellaneous reporting. In a sense, dbcc checkdb is a heavy duty data validation and reporting process that should be offloaded from server A if possible. But, it's just the point about copying the results of the database check on server B back to server A that I'm not sure about.



"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search