Hold my beer. [crayon-6ab2dace45b8b770290745/] 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
#basicbackup So you're taking backups! That's great. Lots of people aren't, and that's not so great. Lots of people think they are, but haven't bothered to see if the jobs are succeeding, or if their backups are even valid. That's not great, either; it's about the same as not taking them to begin with. If…
So you're hosting your SQL Server in the cloud - say Amazon EC2, Azure VM, or Google Compute Engine - and you've noticed that when you're running a backup or a DBCC CHECKDB, you suffer from extreme performance problems.
Queries run slow, and even worse, applications report timeout errors even just trying to connect to SQL Server. More symptoms can include database mirroring and cluster failovers. What's going on?
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:
One of the most popular things in our First Responder Kit is our HA/DR planning worksheet. Here's page one: In the past, we had three columns on this worksheet - HA, DR, and Oops Deletes. In this new version, we changed "Oops" Deletes to "Oops" Queries to make it clear that sometimes folks just update…
I'll get right to the point While you're Direct Seeding, you have to be careful with any other full or differential backup jobs running on the server. This is an artifact of the Direct Seeding process, but it's one you should be aware of up front. In the screencap below, courtesy of sp_whoisactive there's a…
Let's just get one thing out of the way first.
Yes, I understand that you, dear reader, probably hate maintenance plans. You associate them with everything dumb that you did early on in your career, like setting up a single plan that reorganized indexes, then rebuilt them, then updated their statistics. You are older and wiser now, and you swear by tools like Ola Hallengren's maintenance scripts or Minion Reindex.
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…
The statute of limitations has passed, so this week on SQL Server Radio, I get together with Guy Glantser and Matan Yungman to talk about our favorite oops moments. I talked about my very first database disaster ever - done back when I was in my 20s and working for a photo studio, using Xenix, long…
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…
Hidden away in master.sys.databases is one of the coolest diagnostic tools you might ever need: [crayon-6ab2dace54874233553688/] This spiffy little snippet will tell you why each database's log file isn't clearing out. Possible reasons include: Log backup needs to be run (or if you could lose a day's worth of data, throw this little fella in…
There's an old DBA saying...
May you already have a backup restored
A half hour before your boss knows there's corruption
What? There's no such thing as old DBA sayings? Well, maybe if you all said something other than "no" once in a while, you'd be more quotable. Hmpf.
Anyway, this is a serious question! And there are a lot of things to consider
It's easy to get caught up in daily incidents, tickets, and special projects. Like a good scout, though, a core task in a DBA's job is to be prepared. You have to set time aside in your schedule to: 1. Practice responding to corruption. Gail Shaw's "Help, my database is corrupt, now what?" post should…
At its core, backup speed boils down to just two numbers. Question 1: How fast can SQL Server read data from disk? You can measure this by doing a full backup to disk, but use the NUL: option: [crayon-6ab2dace55bdc781658680/] This does a backup, but doesn't actually write it anywhere, so it's a measure of how fast…
You can download the next preview of SQL Server 2016 today, and the announcement blog post touted a few improvements to maintenance plans. Oddly, the screenshots in the announcement were apparently done with Microsoft Paint, so I fired up a VM to see how things really look.
The DBCC CHECKDB step in the wizard now has checkboxes for physical only and tablock:
Most companies come to us saying, "The SQL Server isn't fast enough. Help us make it go faster."
They're kinda surprised when one of the first things we fill out together is a variation of our High Availability and Disaster Recovery Planning Worksheet:
In theory, when you configure AlwaysOn Availability Groups with synchronous replication between multiple replicas, you won't lose data. When any transaction is committed, it's saved across multiple replicas.
That's the way it works, right? I mean, except when you restart your synchronous replicas, or patch them, or they just stop working for any number of reasons. The primary keeps right on trucking, accepting deletes/updates/inserts, without telling end users that all their eggs are in a single basket.
whether it's a new job or a new (old) server
If you're reading this, you've at some point in your career stared at a server, for the first time, with great trepidation.
Growing up as a young boy, my elementary teachers always warned me about the perils of putting stuff in master. But what's the real deal? Why is this such a bad idea? System databases are often located on space-limited drives. The default installation parameters for SQL Server throw master, model, and msdb all on the…
