Backup and Recovery

Backup and Recovery, SQL Server

#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…

SQL Server Timeouts During Backups and CHECKDB

Last Updated February 13, 2017

Amazon Web Services (AWS), Backup and Recovery, CHECKDB and Corruption, Cloud Computing

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…

How to Run DBCC CHECKDB on a Log Shipping Subscriber

Last Updated February 13, 2017

Backup and Recovery, CHECKDB and Corruption, Log Shipping, SQL Server

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: Database cannot be opened. It is in the middle of a restore. Please close the bathroom door. On SQL Server Enterprise Edition, you can try working around it by creating a…

Updated High Availability and Disaster Recovery Planning Worksheet

Last Updated February 23, 2021

Always On Availability Groups, Backup and Recovery, Clustering, SQL Server

One of the most popular things in our First Responder Kit is our HA/DR planning worksheet. Here’s page one: Page 1 – how our servers are doing now, versus what the business wants In the past, we had three columns on this worksheet – HA, DR, and Oops Deletes. In this new version, we changed “Oops”…

Availability Group Direct Seeding: Mind Your Backup Jobs

Last Updated February 9, 2017

Always On Availability Groups, Backup and Recovery, SQL Server

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…

Triage Quiz: Is Your SQL Server Safe?

Last Updated February 13, 2017

Angie Rudduck

Contrary to popular belief, we spend a lot of time with clients when we’re not blogging, answering questions in Office Hours, or working on new features for the download pack. Something we hear a lot is, “How do we compare to other clients?” or “Is this the worst/best setup you’ve seen?”. This got me thinking,…

SQL Server 2016 Maintenance Plan Improvements

Last Updated February 13, 2017

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…

What to Do When DBCC CHECKDB Reports Corruption

Last Updated April 13, 2018

Backup and Recovery, CHECKDB and Corruption, Processes and Practices, SQL Server

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…

My Favorite Database Disaster Stories

Last Updated February 11, 2017

Backup and Recovery, Clients and Case Studies, Processes and Practices

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…

How to Run DBCC CHECKDB for In-Memory OLTP (Hekaton) Tables

Last Updated February 15, 2019

CHECKDB and Corruption, In-Memory OLTP (Hekaton), SQL Server

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…

My Favorite System Column: LOG_REUSE_WAIT_DESC

Last Updated February 9, 2017

Backup and Recovery, SQL Server

Hidden away in master.sys.databases is one of the coolest diagnostic tools you might ever need: SELECT name, log_reuse_wait_desc FROM sys.databases; 1 SELECT name, log_reuse_wait_desc FROM sys.databases; 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…

How often should I run DBCC CHECKDB?

Last Updated February 13, 2017

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…

The Top 4 Job Tasks DBAs Forget

Last Updated February 13, 2017

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…

How to Make SQL Server Backups Go Faster

Last Updated February 9, 2017

Backup and Recovery, SQL Server

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: BACKUP DATABASE MyDb TO DISK='NUL:' 1 BACKUP DATABASE MyDb TO DISK='NUL:' This does a backup, but doesn’t actually…

SQL Server 2016 CTP2.4: Maintenance Plan Changes

Last Updated February 13, 2017

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…

Why RPO and RTO Are Actually Performance Metrics Too

Last Updated February 23, 2021

Architecture, Backup and Recovery, CHECKDB and Corruption, SQL Server

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: Download the full PDF in our First Responder Kit They say things like, “Wait,…

Synchronous Always On Availability Groups Is Not Zero Data Loss

Last Updated February 9, 2017

Always On Availability Groups, Backup and Recovery

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.…

Getting the last good DBCC CHECKDB date

Last Updated February 13, 2017

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. The smarter you are, the greater your trepidation is. The 2nd century mathematician Trepidatius the Wimpy had an equation that described this, but…

Should You Put Things in the master Database?

Last Updated May 16, 2019