PHYSICAL_ONLY looks at page structures and makes sure something isn’t obviously wrong. If you have the database’s page verification option set to CHECKSUM (the default since SQL Server 2005, and a very good thing), it runs a checksum on the page and compares it to a checksum recorded in the header when the page was last modified.
When you use the PHYSICAL_ONLY option, you’re telling SQL Server to skip logical checks. It won’t do things like make sure the data in a clustered and nonclustered index on a table is consistent with one another.
I like the analogy, as well as the how-to guide on messing up your database. But try not to do that to a production database…
Many years ago during an interview I was asked to “Name the top 7 DBCC commands that you use.” I think I was able to name 3. Ever since then I’ve paid particular attention to DBCC commands in preparation of the day when I hear that question again. Well not to long ago I was watching Erin Stellato’s (b/t) PluralSight course “SQL Server: Understanding and Using DBCC Commands”. Note: It’s a great course and I highly recommend it. In it she goes over quite a few DBCC commands, some documented, some not. In noted two in particular.
The first one I noted gives you a list of the size and free space in the logs of all databases on the instance. Highly useful. I’ve used it repeatedly recently. Unfortunately my memory is not always the best and I can never quite remember the exact name. Which brings us to the other command.
I completely agree that Erin’s Pluralsight course is worth watching.
You see the CHECKSUM on the backup along with the RESTORE VERIFYONLY. The code was generated by right clicking on the database, selecting Tasks, then Backup, plug in the parameters, and select Script. I put it in a new query window as I may back up several databases in the same job. Sometimes I’ll just do a find/replace for the other databases since my backup. The Restore Verifyonly gives you some confidence that your backup is recoverable: NEVER assume that just because your backup ran that the database is restorable! The ONLY way to know is to actually restore it to another file! You don’t want to accidentally clobber your production that probably has newer data in it.
Corruption is a serious event when your entire job revolves around protecting data. Be prepared.
This sounds paranoid, but as an example, here’s a corruption case I had recently: shortly after detecting corruption, the team realized they’d have to revert to a backup of the database from a few days ago. Rather than telling users about that possibility, they let the users keep adding data into the already-corrupt database while the DBAs did troubleshooting. Several days later, as the corruption got worse, even Microsoft couldn’t repair the corruption – and the affected tables went completely offline, permanently. If the users would have been alerted earlier, they could have avoided even more data loss.
Good advice. If you have Pluralsight, I recommend Paul Randal’s course on database corruption. Watch that ideally before you have corruption…
We’ve had many of you asking to be part of the Minion CheckDB beta and now is the time. We’re putting the finishing touches on the 1st beta and it’s looking great with some fabulous features.
So this is the open call for beta users. If you’d like to meet Codex before anyone else then send me an email.
We have some requirements though. We don’t want dead beta users. This is your chance to shape the product and we want to hear from you. So if you’re serious about putting the product through its paces then we definitely want you. So you should be ready to provide real feedback, report bugs as you find them, and work with us to fix them.
Considering that I’ve bothered Sean about this at every SQL Saturday the two of us have been at this year, I’d better get moving and join the beta.
THE EASY FIX: RUN FULL NATIVE BACKUPS EVERY DAY, AND FREAK OUT WHEN THEY FAIL.
Backup failures aren’t normally a big deal, but if you use in-memory OLTP on a standalone server or a failover clustered instance, backup failures are all-out emergencies. You need to immediately find out if the backup just ran out of drive space or lost its network connection, or if you have game-over Hekaton corruption.
Note that you can’t use SAN snapshot backups here. SQL Server won’t read the In-Memory OLTP pages during a snapshot backup, which means they can still be totally corrupt.
This works fine for shops with relatively small databases, say under 500GB.
Brent also has a Connect item to fix this. Upvote if you have memory-optimized, durable tables.
So why would you want to do this? Well lets say for example you have a table in a database where the clustered index has become corrupted. Let’s further say that no one mentioned this to you for .. say a year. (No judging!) So your only option at this point might be to use the REPAIR_ALLOW_DATA_LOSS of DBCC CHECKDB. But when you are done how much data has actually been lost? Can you get any of it back?
If you’ve lived a good life and are very lucky, you might recover all data this way. Otherwise, it’s a good idea to run CHECKDB more frequently and check those backups regularly as well.
One of the most important duties of a DBA is to ensure that CHECKDB is run frequently to ensure that the database is both logically and physically correct. So when inheriting an instance of SQL, it’s usually a good idea to check when the last CHECKDB was last run. And ironically enough, it is actually quite difficult to get this information quickly, especially if you have a lot of databases that you need to check. The obvious way is to run DBCC DBINFO against the specific database. This returns more than just the last time CHECKDB was run, and it is not especially clear which row returned tells us the last CHECKDB (FYI the Field is “dbi_dbccLastKnownGood”.)
It’s a bit of a shame that this information isn’t made available in an easily-queryable DMV.
At the end of the day you can’t afford *not* to run CHECKDB- you just can’t. Your database objects are only as reliable as your last clean CHECKDB (meaning your last CHECKALLOC + CHECKCATALOG + individual CHECKTABLE’s on all of your tables – but more on that in a moment).
If you are not running regular CHECKDB, you may have endless unknown corruption across your databases, and you won’t find out until someone tried to access the data.
Even if your production instance is running at 100% all the time (which is a good indicator that you need more resources), you can run CHECKDB against backups restored to a different server.
Andy Galbraith has a tale of woe and a cautionary message:
Paul’s blog post “Issues around DBCC CHECKDB and the use of hidden database snapshots” discusses the need to have certain permissions to be able to create the snapshot CHECKDB uses. I checked the DATA directory and the SQL Server default path and found that the service account did have Full Control to those locations.
What happened next ultimately resolved my issue, and it reflects something I constantly tell people when they ask me how I research things relatively quickly (most of the time anyway :)) – whenever you read a blog post or article about a subject, MAKE SURE TO READ THE FOLLOW-UP COMMENTS! Sometimes they are nothing beyond “Great Article!” but quite often there are questions and answers between readers and the author that add important extra information to the topic, or just “Don’t Forget This!” style comments that add more detail.