A Fine Slice Of SQL Server
Randolph West has a public service announcement:
On behalf of every database administrator everywhere, I implore you not to run
CHKDSKon a system that has a live database installed and running. This includes, but is not limited to:
- SQL Server
- PostgreSQL
- MySQL
- MariaDB
- SQLite
- Oracle
- DB2
Read on to learn why.
Comments closedChris Webb diagnoses a nasty-looking error:
When you’re using Excel workbooks as a data source in Power Query in either Excel or Power BI you may encounter the following error when trying to connect:
DataFormat.Error: File contains corrupted data.
Read on for information about three possible causes.
Comments closedChris 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 closed