Backup and Recovery

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

March 2, 2016

Hidden away in master.sys.databases is one of the coolest diagnostic tools you might ever need: Transact-SQL 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…

How often should I run DBCC CHECKDB?

February 25, 2016

Erik Darling

dba, sql server

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

February 11, 2016

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

December 10, 2015

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

SQL Server 2016 CTP2.4: Maintenance Plan Changes

September 30, 2015

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

September 10, 2015

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

September 3, 2015

Always On Availability Groups, Backup and Recovery

always on, availability groups

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

August 5, 2015

Erik Darling

dba

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?

June 27, 2015

Always On Availability Groups, Backup and Recovery, SQL Server

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…

How to Configure Always On AG Backups with Ola Hallengren’s Scripts

June 25, 2015

Always On Availability Groups, Backup and Recovery, Ola Hallengren's Database Maintenance Scripts

always on, backup, ola hallengren

With SQL Server AlwaysOn Availability Groups, you can offload backups to a replica rather than running them on the primary. Here’s how to do it: 1. Install Ola Hallengren’s utility scripts on all of the replicas. During the install, it creates a laundry list of SQL Agent jobs, but doesn’t set up schedules for any of them.…

How Do You Manage DBAs? Part 1: Measuring Backups

June 22, 2015

Backup and Recovery, Processes and Practices

When the DBA reports to you, ask yourself two questions: “What would I fire the DBA for?” “How can I prove that they’re not doing that?” There’s a lot of possible answers to #1, many of which involve cocaine and the office copier, but let’s focus on one I hear a lot: an inability to restore…

Who’s Backing Up That Database?

June 2, 2015

Kendra Little

Backup and Recovery

A question came up on Twitter recently– how you can you figure out where a rogue backup is coming from? I’ve run into this question with clients before. In an enterprise environment where backup tools come and go, it’s not unusual to find backups being run by some mysterious account. By the way, when’s the…

Find Corrupted Indexed Views with DBCC CHECKDB

April 4, 2015

Kendra Little

If you’ve got indexed views, or are thinking of using them, this is important. First, go read the blog post, “An Indexed View Bug with Scalar Aggregates” by Paul White. It will take a minute, but it’s important to understand. Paul shows a scenario where the data in a certain kind of indexed view can…

9 Ways to Lose Your Data

February 19, 2015

backup, corruption, recovery

Every time someone tells me, “This database is mission critical – we can’t have data loss or downtime,” I just smile and shake my head. Technology is seriously difficult. To illustrate, here’s a collection of client stories from the last few years: The DBCC CHECKDB job ran every week just like it was supposed to…

How Many Databases Can I Put on One SQL Server?

February 4, 2015

Architecture, Backup and Recovery, ISV and SaaS Database Design, SQL Server

backup, dr, ha, recovery, rpo, rto

I’ve seen servers with thousands of databases on a single SQL Server, and it works. Sure, opening the databases list in SQL Server Management Studio is painful, and a lot of third party monitoring tools fall over, but it’s not so bad once you know how to work around these issues. But there’s two issues…

How to Restore a Page in SQL Server Standard and Enterprise Edition

November 11, 2014

Jes Schultz Borland

One of the many restore features in SQL Server is the ability to restore one or more pages of data. This can be very convenient in some narrow situations – for example, corruption occurs on one page or an oops update is made to one record. The page restore process is not straightforward, however, and,…

How to Back Up Terabytes of Databases

July 17, 2014