May 21, 2018 at 8:22 pm
Comments posted to this topic are about the item Checking CHECKDB
May 22, 2018 at 2:06 am
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?
May 22, 2018 at 2:30 am
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.
May 22, 2018 at 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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 22, 2018 at 12:56 pm
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.
May 22, 2018 at 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?
May 22, 2018 at 2:12 pm
jasona.work - Tuesday, May 22, 2018 1:15 PMEric 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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 23, 2018 at 8:53 am
allinadazework - Tuesday, May 22, 2018 2:06 AMHi 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.
May 23, 2018 at 8:54 am
Eric M Russell - Tuesday, May 22, 2018 2:12 PMI 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.
May 23, 2018 at 12:22 pm
Steve Jones - SSC Editor - Wednesday, May 23, 2018 8:54 AMEric M Russell - Tuesday, May 22, 2018 2:12 PMI 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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 23, 2018 at 1:14 pm
Eric M Russell - Wednesday, May 23, 2018 12:22 PMSteve Jones - SSC Editor - Wednesday, May 23, 2018 8:54 AMEric M Russell - Tuesday, May 22, 2018 2:12 PMI 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.
Actually, a potential reason I could see for doing that would be if, prior to running checkdb, the business stands up a virtual machine with SQL, restores a backup to it, runs checkdb against the database, confirms it's OK, then deletes the entire VM. Reasons for doing it this way could be constrained resources on the virtual servers (they have enough to stand up the VM for this, but they can't keep it running all the time.)
Note, I don't say that's a *good* reason, just the first one that popped into my head.
Or, the server that the checkdb is run on is an older physical server, out of warranty with no redundancy of the drives, which then dies. So the question becomes, when was the last time checkdb was run against database A? Can't tell you, the server's dead that has that information and it's not coming back, and the tech who did the check forgot to log it...
Sure, that shouldn't happen, but it will...
May 23, 2018 at 3:07 pm
Eric M Russell - Wednesday, May 23, 2018 12:22 PMIt'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.
If I'm trying to ensure that all systems on ServerA (or B, C, etc) are being checked, how easy is this? Certainly I can setup jobs, and as a DBA, I'd want to do that. I have monitoring for those, and I have alerts that let me know if DBCC returns errors or the jobs fail.
How do I ensure all databases are being checked? If you have a smaller(er) environment, and centralized ways of doing setup, adding databases, or automated items, you might never think that you forget a db, but in a busy environment where you do lots of things, new applications appear, new systems are built up and torn down, it's easy to forget a db is being checked.
What I'd like to do is get this data back to the source database, the one that needs to be checked, to ensure we don't miss any.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply