Integrity checks, corruption findings, and database integrity incidents.
29 associated posts26 primary posts
Every year, Aaron Bertrand runs a curling tournament up in scenic North Bay where he grew up. I joined in this year, and before the tournament, sat on the shores of Lake Napissing to answer your top-voted questions from https://pollgab.com/room/brento.
https://youtu.be/49Ycmorwk_g
Just after passing the Arctic Circle marker and celebrating with a glass of champagne, I took your top-voted questions from https://pollgab.com/room/brento.
https://youtu.be/gyyxKrv45bQ
I tested the April release of the FRK, then answered your questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=AateYX3bUmw
In my last post, I talked about how you can get 43%-67% faster backups. Today, I'm using that same SQL Server setup to discuss how throwing more CPU cores might help you check for corruption faster if you've already got modern fast storage. I don't expect everyone to go to this level of detail by any means, but it's the kind of tuning you can do when you're facing multiple terabytes of production data per server and shrinking maintenance windows.
You want to check for corruption, but you don't want to slow down your primary production server.
In this post, I'm specifically talking about offloading the corruption checking process. I'm not talking about doing corruption checking on both the primary and other servers - that's wonderful, and if you're doing that, you should hug yourself. You're doing a good job. Who's a good dog? You are! Good dog.
You would think that when SQL Server writes your data to disk, it's totally safe.
Forever.
Hard Enough At this point, we've all probably got a database that's a terabyte or more in size. If you're anything like I was, you've got a ton of multi-terabyte databases, and the SAN admin has a special folder for your emails. When you've got a lot of large databases, normal maintenance is out the…
This week, Tara and Richie discuss database corruption, CLR, DBCC issues, adding identity columns to databases, distributed transactions, tracking how recently a procedure was executed, maintenance plans, recompiling stored procs, parameter sniffing issues, troubleshooting replication with Always On, forecasting database growth and estimating capacity issues, tools for database growth forecast reports, RCSI, and much more.
Microsoft has been resting on Paul Randal's laurels for far too long.
From 1999 to 2007, Paul poured his heart and soul into rewriting SQL Server's code to check for and repair database corruption. (For more about his illustrious career, read his bio and enjoy the infectious enthusiasm in his bio photo.)
Disclaimer: I love Azure SQL Managed Instances (MIs) so far. They're really cool, and I'm having a fun time playing with them. I'm about to describe a bug, and I'm sure this bug will be fixed soon, so it shouldn't stop you from previewing Managed Instances. This post is not "MIs are t3h suxxors" -…
You've been such a good database administrator.
You followed the setup checklist in our First Responder Kit. You ran sp_Blitz. You set up email alerts for common issues. You run CHECKDB as frequently as practical - weekly, or maybe even daily.
So far in this series, I've shown you how to corrupt a clustered index, then how nonclustered indexes can be corrupted independently. If you haven't read those, you should start there first.
Let's start again with our 50Ways database, but this time we're going to be good and make sure that we've got checksums enabled, and that we're in full recovery model:
We got an email!!! It was weird. It was from an Australian, and it was right side up. I could hardly believe it. Normally we encourage people to ask us questions during Office Hours, or over on Stack Exchange. That way lots of people can benefit from the answer. But since this reader is on…
In part 1, I showed you how to corrupt a database - not because you'd need to do it, or because SQL Server has some kind of vulnerability. I just wanted to show you how SQL Server reacts when it finds corruption in a data file, why you should check your databases for CHECKSUM settings, and set up alerts so you can react fast.
Hold my beer. [crayon-6a6ece3f0ac23736279918/] Now, let's corrupt it. Open it with a hex editor - my personal favorite is the free xvi32 because it doesn't require installation. Just download it, fire it up (you'll want to run it as administrator), and open the database's MDF file: Next thing you know, you're looking at the contents…
Companies call us for performance or high availability issues, but over and over, the very first two things we find are:
They're not taking backups to match the business's RPO and RTO
They're not doing CHECKDB weekly, or at all, and don't understand why that's an issue
Don't make me regret publishing this
This really will take a server down immediately. You can restart it, as we learned during DBA Days, but... If you also go ahead and make this an Agent job that runs at startup, there are additional challenges.
Like every time Agent starts up, your server goes down. Score one for Express Edition, I suppose.
At first glance, if you set up log shipping the way most folks do, it looks like you can't run CHECKDB on a subscriber:
On SQL Server Enterprise Edition, you can try working around it by creating a database snapshot - but that won't work either:
The instant you encounter corruption in a production SQL Server database, stop. Read this entire article first to understand the big picture, and then come back here to take action. First, understand that you shouldn't start by trying to repair the corruption. You may be facing a storage subsystem problem where your storage is corrupting…
tl;dr - run a copy-only full backup of the Hekaton filegroup to nul. If the backup fails, you have corruption, and you need to immediately make plans to either export all your data, or do a restore from your last good full backup, plus all your transaction log backups since. Yeah, that one's gonna need a…
