Production Database Administration

Home
Production Database Administration

DBA Training Plan 10: Managing Index Fragmentation

Last Updated May 7, 2020

In the last episode, we looked at your index designs with the output of sp_BlitzIndex. You might have gotten a little overwhelmed what with all the different warnings (and all the learning resources!) You might have thought, “Is there an easier way?” “Is there a way I can just run a job, and have something…

DBA Training Plan 9: My 3 Index Guidelines

Last Updated August 15, 2019

Brent Ozar

DBA Training Plan, Indexing

In our last episode, we used sp_BlitzIndex @Mode = 2 to get to know the contents of our database, sorted our indexes from biggest to smallest, and started asking questions about leftover backup tables that we probably didn’t need to keep around anymore. Now that you’ve taken out the trash, let’s have a closer look…

DBA Training Plan 8: What’s Inside Your Own Databases

Last Updated August 13, 2019

Brent Ozar

DBA Training Plan, Indexing

In the last episode of the DBA Training Plan, we talked about what’s inside the MDF file: a series of pages that store a database’s tables. Now, it’s time to check out the contents of YOUR database to understand what’s using the most space, what’s getting used the most often, and what you might be…

DBA Training Plan 7: How SQL Server Stores Stuff in MDF Files

Last Updated August 13, 2019

Brent Ozar

DBA Training Plan, Indexing

So far in the DBA Training Plan, we’ve been working hard to make sure the contents of our MDF files are backed up, corruption-free, and secured. Now, uh…what’s actually inside them? Inside each of your database data files (MDFs, although technically you can use any extension you want, even PDF or MP3), SQL Server stores…

DBA Training Plan 6: How to Monitor Drive Space

Last Updated August 13, 2019

Brent Ozar

DBA Training Plan, Monitoring

Don’t. It’s not your job. No, seriously, hang on a second. I’m not saying that you should ever say things like “It’s not my job,” but I do want you to understand when you should avoid reinventing the wheel. Your Windows administrators should be monitoring every Windows server you have, making sure it doesn’t run…

DBA Training Plan 5: Knowing Who Has Access

Last Updated August 13, 2019

Brent Ozar

DBA Training Plan

Who can get you fired? Run our free sp_Blitz stored procedure on one of your production servers and pay particular attention to the Security section of the results. It lists the logins who have been granted the sysadmin or security admin roles. Don’t think of them as logins. Think of them as people who can get…

DBA Training Plan 4: Checking for Corruption

Last Updated August 13, 2019

Brent Ozar

CHECKDB and Corruption, DBA Training Plan

You would think that when SQL Server writes your data to disk, it’s totally safe. Forever. You would be wrong. It’s almost like there are gremlins out to get you, purposely trying to trash your data. In the really old days, it was problems with magnetic hard drives. Later, it was buggy shared storage arrays.…

DBA Training Plan 3: Treat Your Servers Like Cattle, Not Like Pets

Last Updated August 13, 2019

Brent Ozar

Backup and Recovery, DBA Training Plan

In our last post in this series, I talked about why you should be using Ola Hallengren’s maintenance script for backups and sp_DatabaseRestore for faster restores. These two tools have implications for how you build, back up, recover, and fail over your SQL Server. There’s a saying in the cloud: treat your servers like cattle,…

Nines don't matter if users aren't happy

DBA Training Plan 2: Backups (And More Importantly, Restores)

Last Updated August 13, 2019

Brent Ozar

Backup and Recovery, DBA Training Plan

When I first started out as a SQL Server DBA, I thought things were going well as long as the backup jobs ran successfully. I’d go into SQL Server Agent every now and then, make sure they were still running, and … that was the end of it. I figured if disaster ever struck, I’d…

DBA Training Plan 1: Build a Server Inventory

Last Updated July 19, 2019

Brent Ozar

DBA Training Plan

At your company, walk into the VP of Sales’s office and ask them how many salespeople they have. NO, I mean, don’t actually DO that, because he’s going to ask you why the sales app is so slow. But I mean, imagine if you COULD walk into his office and ask him that. I bet…

Welcome to the DBA Training Plan.

Last Updated July 19, 2019

Brent Ozar

DBA Training Plan

Foreword: 8 years ago, I launched an email series with a 6-Month DBA Training Plan. I sent one email per week, bringing people up to speed on the most important topics that nobody taught ’em along the way. It’s hard to believe it’s been 8 years! This month, I’m revisiting the emails, updating their content,…

Things to Think About When Your Databases Grow Quickly

Last Updated June 22, 2019

Brent Ozar

Production Database Administration

In SQL ConstantCare®, we’re adding a notification for when your data size grows >10% in a single week. When this happens, you want to stop for a second to ask yourself a few questions: Did someone just create a backup table? Folks often do a SELECT INTO to back up an important table right before…

What Happens to DBAs When We Move to the Cloud?

Last Updated June 9, 2019

Brent Ozar

Production Database Administration

I’m finishing up a bit of a summer tour – Techorama Belgium, SQL Saturday Dallas, SQL Saturday South Florida, and this week, SQL Intersection in Orlando – and I’ve gotten the same question from several attendees. It goes something like this: “My company hasn’t moved to the cloud yet, but at every conference session, I…

Things SQL Server Setup Doesn’t Do – And Should

Last Updated May 30, 2019

Brent Ozar

Production Database Administration

During the setup wizard, SQL Server should ask for your email address. It should suggest that you use a distribution list for people on your team, so that someone gets the alerts when you’re out on vacation. Then, it should send these common failure alerts to you rather than making you set them up manually.…

5 Questions to Ask When You Upgrade SQL Server

Last Updated May 29, 2019

Brent Ozar

Production Database Administration

Whether you’re looking to go cutting-edge and hop on SQL Server 2019 as soon as it’s available, or whether you’re just trying to evacuate from SQL Server 2008 to anything that’s going to be supported after this summer, you’re probably doing upgrade projects. This is a good time to ask yourself 5 questions: 1. Do…

What You Said You’re Going to Do About SQL Server 2008

Last Updated March 29, 2019

Brent Ozar

Production Database Administration

Last week, I asked you what you were going to do about SQL Server 2008’s fast-approaching end-of-support date. Countdown clock at SQLServer2008.com Here were some of my favorite responses from blog comments and Twitter. Terry’s been doing a good diligent job, but struggling to keep up: Like Andrew we’ve been working on retiring/migrating off 2008…

What should you do about memory dumps?

Last Updated August 11, 2020

Brent Ozar

Production Database Administration

A SQL Server monitoring tool is warning you that you’re getting memory dumps, and you’re wondering what that means. This isn’t normal: you’re encountering a situation where SQL Server is so freaked out that it’s unable to proceed normally with a query. In order to help you (and really, Microsoft) debug the problem, it’s calling…

[Video] How to Capture Baselines with sp_BlitzFirst

Last Updated October 15, 2019

Brent Ozar

Monitoring, sp_BlitzFirst, Videos

Everybody tells you to capture baselines – but what exactly are you supposed to capture? Which Perfmon counters matter? How do you track which data files are growing, and which ones are slow? How can you track which queries are using the most resources? All you have to do is install the free, open source…

Should you run SSAS/SSIS/SSRS on the SQL Server?

Last Updated February 12, 2019

Brent Ozar

Architecture, Configuration Settings

When you’re building a new SQL Server, you’re going to see a few intriguing checkboxes during setup. It’s all free, right? Check everything! The services are all free, right? You can just check the boxes for Machine Learning Services, R, Python, Data Quality Services, PolyBase, Integration Services, Analysis Services, and Reporting Services – it’s not…

What Queries Does Microsoft’s CEIP Service Run On Your SQL Server?

Last Updated August 9, 2020

Brent Ozar

Monitoring

You’ve seen the CEIP Service on your SQL Server, and you’re wondering what queries it runs and how it sends that information back to Microsoft. I was wondering too, because I started seeing queries running that I didn’t expect: sp_WhoIsActivelyRunningTelemetryQueries (click to see full size) Ah-ha, the telemetry service, also known as SQLCEIP! Starting with…