A Fine Slice Of SQL Server
Chris Taylor has a tale of woe:
Late last week (20th) we had an emergency call from a company that had a production database go into Suspect mode and needed help. Now this isn’t a great situation to be in so when they then go on to tell us that the last valid backup they had was from the 12th and the backup job had been failing since then – even less of a great situation to be in
Read the whole thing.
Comments closedChad Callihan isn’t in production:
When you’re not expecting it, database corruption is bad for your database server (and probably your own personal health). Database corruption can be fun though when you’re the one doing the corrupting. Let’s take a look at corrupting a database and see what we can learn.
Click through to see what kind of madness you can pull off and how to fix it. And if you get really excited about database corruption, check out Steve Stedman’s Database Corruption Challenge.
Comments closedGrant Fritchey reviews a product which attempts to repair corrupted SQL Server databases:
Let’s start with the most important piece of information you need: it works.
The software itself is really simple to use and just does what you need, repairs your corrupted SQL Server instance. On that alone, I can recommend the tool.
However, there are a few gotchas I ran into along the way. Mostly, little stuff. It’s things a little polish in the UI and some clean up around language could help out. Don’t get me wrong, I’m happy with this software. It worked. It’s just how it works that we should talk about.
Click through for Grant’s full review.
Comments closedBrent Ozar follows Betteridge’s Law of Headlines:
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.
Now, for the rest of you – your production server is slow, and you wanna know what the drawbacks are of running CHECKDB on other servers instead. Here are the issues to consider.
Click through for the list of drawbacks.
Comments closedErin Stellato shows how you can track the results of those automated CHECKDB runs you’re doing:
First, you need to be running CHECKDB on a regular basis. If you’re going to ask what you mean by regular basis, I’ll direct you to a post of Paul’s that is still relevant: Importance of running regular consistency checks. There are a few factors to consider, but in general I recommend at least once a week. If you’re a 9 to 5 shop and have the maintenance window to run CHECKDB daily, feel free to do that.
Erin walks us through it and also recommends checking out Ola’s scripts for integrity checks. I’d add to that Minion CheckDB.
Comments closedDaniel Janik takes us through an ordeal related to CHECKDB on an Azure Managed Instance:
This is crazy! What now? Open at ticket with MSFT? This seemed the only choice and what was the root cause? Apparently in Azure Managed Instances, Microsoft will check databases for corruption and will take the database offline if detected.
When in this special offline state there’s no way to access the database and Microsoft must be contacted. You can’t set the DB in recovery mode or change it to ONLINE. Microsoft does “contact” someone to notify that the database was taken offline due to corruption but if you work at a larger company this notification may never reach the right people.
Read on to see what Daniel ended up doing and some tips on making the process smoother.
Comments closedPaul Randal shares some wisdom on corruption:
We’ve had a few new clients come to use recently after experiencing corruption, and they’ve been worried about whether physical corruption can propagate to secondary databases (like an availability group secondary or log shipping secondary) through the mechanism used to maintain the secondary database in sync with the primary database. I explained how it’s very rare for that to happen, but sometimes it does, and not in a way you’d think. Read on…
I don’t even have to ask you to read on; Paul has even done that. And do read the comments as well.
Comments closedPaul Randal explains a long-standing bug in CHECKDB:
Continuing the database snapshot theme from the previous Curious Case post, I had another question from someone who was concerned about transactions rolling back during DBCC CHECKDB. They’d just noticed the messages in the error log saying that when DBCC CHECKDB was executed, it was causing transactions to roll back in the database – and how could that possibly be allowed to happen? They said they panicked and stopped all DBCC CHECKDB executions.
There’s no need to panic. The problem is actually a bug in the database snapshot code that’s been there since SQL Server 2005, where it reports the wrong database name.
Read on for a demo; it’s not a dangerous problem.
Comments closedPaul Randal answers a reader question:
I had a blog comment question a few days ago that asked why emergency-mode repair requires the database to be in EMERGENCY mode as well as SINGLE_USER mode.
All repair operations that DBCC CHECKDB (and related commands) performs require the database to be in single-user mode so there’s a guarantee that nothing can be changing while the checks and repairs are done. But that doesn’t change the behavior of what repair does – that needs emergency mode too.
Read on for an explanation of what emergency mode is and why we need it to run CHECKDB repair operations.
Comments closedArun Sirpal explains an error message on Azure SQL Database:
msg 7928, Level 16, State 1, Line 3
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 8921, Level 16, State 3, Line 3
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Read on to see what this means, as well as what it means for you.
Comments closed