• Skip to main content
  • Skip to primary sidebar

DallasDBAs.com

SQL Server Database Consulting

  • Services
  • Pocket DBA®
  • Blog
  • Testimonials
  • Contact
  • About

Beginner

The Ironic DBA Files—Episode 9: The Rise of the Clients

August 6, 2019 by SQLandMTB Leave a Comment

Welcome back to The Ironic DBA Files, a series where the newbie DBA on staff at Dallas DBAs chronicles the ups and downs of fast-tracking a new career as a DBA.

It’s go time, ladies and gentlemen. Last week we reviewed indexes and the basic differences between reorganizing and rebuilding those indexes. Now, it’s time for the big reveal:

I’m allowed to touch client PROD servers!

A Funny Thing Happened on the Way to the Server

Thankfully, I didn’t have a ton of nerves logging into a client server for the first time, for two reasons.

First, as a WordPress developer, I’ve been in client accounts and such before. I’m aware that a company’s data can be more critical and sensitive than a WordPress blog, but both are important to that client. It doesn’t matter if it’s a multi-million dollar company or a food blogger’s site, you want to do your best and make sure you’re careful to do things correctly. Plus, one of my former WordPress clients makes seven-figures a year off her blog, so I can literally say her blog is every bit as critical as a company’s SQL Server database.

Second, our approach here at Dallas DBAs is very careful. Before ever logging into a client’s server on my own, Kevin first screen shared with me and I watched as he went through practices and procedures. Then, each day for the rest of the week we screen shared as he coached me through logging in and doing a server review. Only after a week of his oversight did he release me to begin solo server reviews on one client’s server. A couple of weeks later we went through the same process all over again with a second client. I’ll be adding a third and fourth client via the same processes in the coming days.

For now, my one and only task is to log into client servers daily and look for problems. I’m learning how to identify immediate red flag issues such as long waits, locks, and blocks. I’m checking the SQL Server error logs for anything out of the ordinary. I’m ensuring backups are up to date. And I’m learning how to effectively use monitoring tools such as Manage Engine, Solar Winds DPA, and Red Gate SQL Monitor.

As I go through this daily process, I’m learning how to properly report a server’s status to our clients, identify problems, and escalate those problems to Kevin. As an apprentice, it’s not my role to attempt to fix problems, but rather to stay engaged and learn how Kevin approaches making things right so that as I progress I add more and more knowledge to my tool belt.

Does This Look Right to You?

SQL Server Waits
One of these things is not like the others.

This is going to sound bad, but it’s sort of fun when a client’s server “breaks.” Let me explain.

If SQL Server and the underlying hardware always ran flawlessly then there wouldn’t be any need for DBAs, would there? In a way, being a DBA is mostly sitting around waiting for something to go wrong.

That’s not all there is to it. There’s consulting available where some DBAs help clients with architecture and hardware questions. A DBA might be consulted by a client who wants to upgrade their servers or systems. But let’s admit that the main action parts of the job involve putting out fires.

Those fires might be small, such as a client asking, “Why is this query so slow?” or “Can you help us implement a better maintenance schedule?” Other times it’s a 3 AM call from a client who’s panicking because someone accidentally dropped a server, a server died, a restore failed, or something else potentially catastrophic. That’s when a DBA really gets to serve their clients.

For me, that’s really the most exciting part about learning to be a DBA. What I do can have a real, significant impact on a client’s business and well-being. It’s an opportunity to serve.


If you take a look at the graph just above, as well as the graph further up the page, you’ll see an example of how my first week looking at a client’s server went. Long story short, this client had some growing pressure in their server in terms of wait and blocks. There were some regular overnight queries that were taking longer and longer to execute. Eventually this blocking was spilling over into the next hour and affecting subsequent performance.

Wanting the best for our client, I found myself feeling this strange mix of concern and joy over the situation. Concern that we as their DBAs needed to fix this, and joy that we were in a position to help. I had these feelings even though my only contribution to the whole incident was basically saying, “Hey, this looks wrong!” and escalating it to Kevin. But still, I had an opportunity to contribute and it was fulfilling.

In the end, it was an easy fix for our client. They discovered the query that was causing the issue was not critical and simply prevented it from automatically running from within their application. Problem solved. In fact, their server is now performing much better overall than it did before.

What’s the main point of all this, other than simply sharing more about what I’m learning? If you’re out there struggling to figure out what sort of work you should get into, I highly recommend finding that sweet spot where you not only find something you are good at and enjoy doing, but something that also allows you to serve others in some capacity. Serving others is where the long term rewards of any endeavor truly come from.

That’s all for this week. Join me next time for The Ironic DBA Files—Review One: A SQL Story.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.

The Ironic DBA Files

      • Prequel: The Ironic DBA—Starting a New and Unexpected Career
      • Episode 1: You Back That Up?
      • Episode 2: Attack of the Corruption
      • Episode 3: Revenge of the Index
      • Episode 4: A New Primary Key
      • Episode 5: The Maintenance Plan Strikes Back
      • Episode 6: Return of the TSQL
      • Episode 7: The Backup Awakens
      • Episode 8: The Last Rebuild

Follow @Dallas_DBAs

Filed Under: Accidental DBA, Beginner, Career, EntryLevel

Free SQL Server DBA training – Dallas 2019

August 1, 2019 by Kevin3NF Leave a Comment

Save the Date!

September 18, 2019 – Dallas, TX

8am – 5pm, lunch provided (probably pizza or Jason’s Deli)

Thanks to the awesome folks at Matrix Resources for donating their training room, we are able to offer a one-day DBA Fundamentals training class at NO charge!

This class is designed to be an overview of SQL Server for non-DBAs.  System Admins, developers, Oracle DBAs, Managers and anyone else that wants or needs to know more about how SQL Server works, We have presented this day of training at multiple SQL Saturday events as a pre-conference session.

Previous attendees said:

  • “Very Good excitement & interest in topic”
  • “Very engaging, knowledgeable, friendly.  Good session”
  • “Very informative and gave real life experiences.”
  • “I enjoyed the broad explanations helpful to beginners as well as those who are already DBA’s.”
  • “Kevin/Instructor was very knowledgeable and the information shared in the presentation was clear; presented well. I like the fact that it included to bases for those that were new to SQL”
  • “Perfectly fits the entry level need for understanding SQL Server.”
  • “Easy to understand.”
  • “First 7 or 8 hour presentation that I have ever been to where I did not drift off from the presentation mentally. This presentation kept me 100% engaged every minute.”

Check out this promo video to get an idea of what we will be learning:

Or, the non-video version:

I will teach you SQL Server fundamentals so that you will have confidence in:

• What makes up a SQL Server

• How a database is structured

• How to install and what to look out for

• Finding what you need in Management Studio

• SQL Security Basics

• How to create and manage backups

• How to create a database

• Picking a Disaster Recovery option for your environment

* topics may change on the fly depending on what the class wants

These are taught from a foundational standpoint, in simple to understand terms so that you can build on them with confidence. My teaching is heavy on interaction, with examples from years of experience/mistakes. There will be a mix of slides and demos. I’ll skip many advanced features that are out of scope of a beginner class, as I don’t want your head to explode! Join me for the day and you will walk out with a strong grasp of SQL Admin basics!

A word of caution – if you are already a SQL Server DBA with even a year of experience, you may be very bored in this class…

A special thank you to Pam Gates for helping reserve this training room at no charge to Dallas DBAs, so that we can offer this class for free!

Sign up here! There are only 24 spots available, due to the size of the TV I will be projecting onto.  I might open up more once I go in person and see if the farthest seats can see the screen clearly.

Thanks, and we look forward to meeting you!

Kevin3NF and the Dallas DBAs team

 

 

 

 

Filed Under: Accidental DBA, Beginner, Training

The Ironic DBA Files —Episode 8: The Last Rebuild

July 30, 2019 by SQLandMTB 1 Comment

Welcome back to The Ironic DBA Files, a series where the newbie DBA on staff at Dallas DBAs chronicles the ups and downs of fast-tracking a new career as a DBA.

Last time we took a quick look at getting started learning TSQL, and this week will keep it pretty basic as well. Let’s talk about indexes again.

Indexes Are Part of the Job

These bikes appear to be indexed.

Again? Yep, indexing properly is really the soul of how SQL Server operates. There’s a lot of physical and logical processing going on under the SQL Server hood, but server performance can live or die depending on the quality of the indexes.

Don’t believe me? Take a few minutes to watch Brent Ozar (b|t) give a quick tutorial on how to use the sp_BlitzIndex to see just the tip of the iceberg of indexing issues. (I won’t spoil it, but some of the terms for indexing problems are hilarious.)

Before we go too far here, let’s not get lost in the weeds. I’m learning that much of a DBA’s job is figuring out why SQL Server is running slowly. There are many reasons that could cause a server to bog down—from hardware issues to poorly written queries—and bad indexing is just one of those reasons.

Also, let’s always keep in the back of our mind that our job is to protect the data, so make sure you’ve got a handle on the basics of DBCC CheckDB and backups. If you don’t, you’re not ready for indexes.

Check out this quote from Erin Stellato (b|t) on why indexing takes a back seat.

I consider consistency checks, often referred to as CHECKDB, as one of the next most important tasks for a DBA.  And if you’ve been a DBA for a while, and if you know how much I love statistics, you might wonder why fragmentation and statistics take third place.  Well, I can fix fragmentation and out-of-date/inaccurate statistics at any point. I can’t always “fix” corruption.  https://www.sqlskills.com/blogs/erin/the-accidental-dba-day-13-of-30-consistency-checking/

By the way, if you haven’t read the Accidental DBA Series at sqlskills.com yet, what are you waiting for?

Indexes Need Love and Attention

Clustered index…c. 1950.

I have a perception that the typical Accidental DBA—once they learn about the power of indexes—takes a “set it and forget it” approach. It might even be tempting to sell ourselves the line, “Well, a bad or out-of-date index is better than no index.”

The problem is the F-word. Yep, I’m going there. I’m going to use the F-word on the Dallas DBA’s site and hope I don’t get fired. (Fired is a completely different F-word.) Here it is:

Fragmentation.

Do you ever remember the need to defragment your computer’s hard drive? It was periodically necessary because, as your operating system created, deleted, or changed information on your computer, the data was often seemingly placed willy-nilly around the hard drive’s available space. A document you created might literally have its bits split up and stored on different sectors of your HD, meaning the computer had to search harder to find all of the disparate file fragments every time you wanted to open that document.

The same basic principle is true in SQL Server. Remember back to the very beginning of your SQL Server studies when you were working on learning the underlying structure of how everything worked? Let’s review.

If you recall, all of the data in SQL Server’s tables is stored in 8K pages. Every time data is modified in a table then data is modified in related indexes as well. (Yes, I’m oversimplifying.) This means that the underlying data on those 8K pages gets shuffled around as necessary—fragmentation.

According to the MS Docs, “Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly, especially scan operations.” 

So, what to do when your database is fragmented and begins running poorly? You’ve got two basic options—REBUILD and REORGANIZE—and you need to understand the differences between the two.

REORGANIZE: A REORGANIZE operation uses lower system resources than a REBUILD. It defragments the clustered and nonclustered indexes at the leaf level by physically reordering the pages to match the logical order of the leaf nodes. REORGANIZE also compacts the index pages based on the existing fill factor value.

REORGANIZE is performed online so table locks are not held. This means queries and modifications are not blocked during a REORGANIZE operation. REORGANIZE also does NOT update the statistics for the index, so you may need to consider an additional UPDATE STATISTICS operation.

REBUILD: A REBUILD operation completely drops and recreates indexes. This index rebuild removes fragmentation and compacts the pages based on a specified or existing fill factor setting. A REBUILD operation reorders the index rows in contiguous pages.

Since a REBUILD operation destroys and recreates indexes, it is a blocking procedure. Queries will be unable to access the index pages until the operation is complete. Because the REBUILD operation essentially recreates the index it must update the index statistics, so there is no additional need to perform an UPDATE STATISTICS operation.

Wait! Statistics? What’s that?

That, my friends, is a topic for a future discussion…

But wait! What’s this fill factor thing? (Gah! That’s two more F-words!)

Check out Brent Ozar’s explanation: “‘Fill factor’ is a setting for indexes in SQL Server. When you create or rebuild an index, you can tell SQL Server what percentage of each 8k data page used in the ‘leaf’ level of the index it should fill up.

In other words, if you set a 90% fill factor when you rebuild a clustered index, SQL Server will try to leave 10% of each leaf page empty. The empty space will be present on the page as it’s written on disk, and it’ll take up the same amount of space as it sits in memory.

By default, SQL Server uses a 100% fill factor and tries to fill up all the pages in indexes as close to full as it can. Depending on how many rows actually fit on the page.”

Does it seem like I’ve left a lot out that you should learn? Yes, I have. There’s only so much room in a single blog post and you owe it to yourself and your career to go do some digging on your own.

Hey, Senior DBAs! What are some issues related to this rebuild vs. reorganize discussion a newbie needs to know about? Tell us something you’ve learned that you wish you had known when you were a Junior. The comments await your wisdom.

That’s all for this week. Join me next time for The Ironic DBA Files—Episode 9: The Rise of the Clients.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.

The Ironic DBA Files

      • Prequel: The Ironic DBA—Starting a New and Unexpected Career
      • Episode 1: You Back That Up?
      • Episode 2: Attack of the Corruption
      • Episode 3: Revenge of the Index
      • Episode 4: A New Primary Key
      • Episode 5: The Maintenance Plan Strikes Back
      • Episode 6: Return of the TSQL
      • Episode 7: The Backup Awakens

Follow @Dallas_DBAs

Filed Under: Accidental DBA, Beginner, Career, EntryLevel

The Ironic DBA Files—Episode 7: The Backup Awakens

July 23, 2019 by SQLandMTB Leave a Comment

Welcome back to The Ironic DBA Files, a series where the newbie DBA on staff at Dallas DBAs chronicles the ups and downs of fast-tracking a new career as a DBA.

In the last post I talked about how I don’t enjoy coding all that much and how I once dreaded the idea of being a DBA due to the use of TSQL here and there. I’ve gotten over that fear and made some pretty decent strides in beginning to understand how the code works. Getting a basic grasp on the syntax does wonders for understanding what’s going on in a particular script.

You Don’t Know What You Don’t Know

I’ve had a practice Azure VM with an instance of SQL Server 2016 installed almost since day one in my training. The purpose of the VM is to give me a place to get familiar with the workings of SQL Server and it’s associated tools like SSMS.

I wrote a couple of weeks ago about implementing Maintenance Plans on my VM. I was taking regular backups and performing regular maintenance on the instance’s databases, but not really doing anything with it. In my studies, I’ve read and heard over and over that “you don’t have a valid restore plan until you’ve practiced restoring your backups.” So, I decided it was time to get that done.

I decided to approach my restore plan as if I were restoring to an off-site Dev or QA instance. I don’t have one of those, but Kevin had recently added a second, larger E: drive to the VM. So my thought process was to install a new instance of SQL 2016 on the E: drive and then restore my backups to that instance.

This was also a good refresher on installing and updating a new instance to the most current SP and CU. One of the things I’m seeing often when Kevin works with clients is that some of their server issues can be at least partially resolved by simply updating to current SPs and CUs. I’ve tried to internalize this and make it part of my regular instance install routine.

Once my instance was up and running, I started searching for the best way to restore my backups to the new instance. I found the script below and gave it a go.

RESTORE VERIFYONLY
FROM DISK = N'F:\SQLskills\20130609_SalesDB_Full.bak
WITH CHECKSUM;
GO

As you might see, this script was taken from sqlskills.com, and I had to tweak it be valid for my server, such as changing VERIFYONLY to DATABASE and naming the correct database and file names and locations on my E: drive. Simple, right?

Not. Even. Close.

I can’t go into much detail here simply because it would make this post as long as a short novel. Long story, short, the rest of my day and part of the next day was a revelation of rookie mistakes, including, but not limited to:

  • Unfamiliar drive and folder permission problems on Windows
  • Forgetting about Recovery Models when not finding log backups for some databases
  • Not checking the error logs like I should have when problems arose

This sort of thing went on and on before I eventually threw in the towel and asked Kevin for help. He got me sorted out pretty quickly—as a Senior DBA should—and I was back on track restoring my backups.

Rookie Mistakes are Expected

Guess what? Rookie mistakes are part of the process. It’s very much like riding a mountain bike. You are going to crash when you push yourself to the next level. You’re going to forget how to handle certain difficult situations and need guidance.

What this really means is you’re striving to move forward. Learn from your mistakes and add the new-found knowledge and skills you earned from failure and recovery to your tool kit.

You see, not only is Kevin my Senior DBA guiding me through all of this, he’s also been my mountain bike coach since I started riding trails. This is why I emphasized last week about the need to find a mentor if you don’t already have one.

When I’m riding trails alone I tend to be much more cautious and stay well within my skills and experience. However, when I’m riding with others, especially when I have Kevin in front of me showing the way, I’m much more apt to push myself and try new things—even things that scare me.

That was my approach during this whole backup/restore episode. I wasn’t on a Prod server and I knew that Kevin was right there to help me when I needed him. He patiently showed me not only what I was doing wrong, but pointed out things I didn’t know about or had forgotten such as looking at all the options in the Restore Database dialog to properly name my databases and select the file locations.

As a side note, I mentioned before about how part of my problem was a permissions issue that was specifically a Windows thing. I’ve noted before how I’m a long-time Mac user, and one of my main problems in this restore process was simply the differences in the Windows file tree system. Even once I was able to restore my databases properly they were not getting saved where I really wanted them because of one simple click—an unnecessary click in the Mac OS Finder, but absolutely necessary in Windows Explorer.

It’s all part of the process, and I’m getting there one step at a time. Even if some days it’s two steps forward and one step back, progress is being made.

That’s all for this week. Join me next time for The Ironic DBA Files—Episode 8: The Last Rebuild.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.

The Ironic DBA Files

    • Prequel: The Ironic DBA—Starting a New and Unexpected Career
    • Episode 1: You Back That Up?
    • Episode 2: Attack of the Corruption
    • Episode 3: Revenge of the Index
    • Episode 4: A New Primary Key
    • Episode 5: The Maintenance Plan Strikes Back
    • Episode 6: Return of the TSQL

Follow @Dallas_DBAs

Filed Under: Accidental DBA, Beginner, Career, EntryLevel

The Ironic DBA Files—Episode 6: Return of the TSQL

July 16, 2019 by SQLandMTB Leave a Comment

Welcome back to The Ironic DBA Files, a series where the newbie DBA on staff at Dallas DBAs chronicles the ups and downs of fast-tracking a new career as a DBA.

Last week I revisited the need to have a good understanding of Maintenance Plans, even if you end up rarely using them later on in your DBA career. As with all the other topics I’ve touched on so far, having a good understanding of the fundamentals will give you a solid foundation to start building your skills and knowledge upon. This week, let’s talk about code, specifically T-SQL.

typing code

Let Me Tell You What I Really Think

Honesty time, here—not that I don’t strive to be honest all the time anyway—I hate coding. I know how to code various languages at differing levels of proficiency, but I’ve never really enjoyed the process of learning a new language and parsing it. This is the number one reason why I avoided looking into SQL DBA as a career when Kevin first hinted at the possibilities a couple of years ago. The thought of sitting in front of a computer and banging out code for most of the day was a non-starter for me.

Thankfully, I learned that becoming a DBA involves some code wrangling, but not anywhere near as much as I expected. In fact, the early days of my training had me pretty much avoiding TSQL as much as practical so that I could concentrate on all the things I’ve previously written about.

However, there came a point where there was no option but to begin learning the basics of TSQL so I could better understand what was going on under the hood of SQL Server. Plus, though there are often multiple ways to get something done in the SSMS GUI, there are times when writing a quick script or copy/pasting a script is faster, more flexible, and more reliable for a particular database’s needs.

I’m not here this week to tell you I’m now a TSQL guru by any means. I’ve still got a long way to go, though I’m fairly comfortable writing very basic scripts and parsing slightly more complex ones. But I still hit a wall pretty quickly in understanding what’s going on within even a slightly long or complex script. That’s ok, I’ll get there.

A Few Tips I’ve Learned

  • First up, if you aren’t blessed to have a mentor guiding you along the path, do everything you can to find one. I can’t tell you how many times I would have been completely lost without Kevin there to fill in the gaps.
  • Second, don’t be afraid to read stuff that’s over your head. Just don’t spend too much time trying to figure out what it means if you’re not grasping it right away. Case in point: I often take the time to Google stuff and land on either SQLSkills.com, SQLAuthority.com, or BrentOzar.com. Those three sites have become my go-to sources when I want to figure out how to fix something or better understand how SQL Server works.
    The difficulty is that these sites often go beyond my current levels of understanding fairly rapidly, especially when they start using script examples. That’s fine. Getting exposed to TSQL and reading explanations about what the scripts accomplish help me gain familiarity.
  • Third, don’t expect to fully understand a script unless you understand what it’s supposed to accomplish. This sounds similar and somewhat backward from my last point, but often I run into difficulty understanding a script because I simply don’t grasp what the script is doing to the database.
    For instance, one of the tasks Kevin had me perform recently was to create a new Filegroup on my practice StackOverflow database, and then move that Filegroup file to a new drive on my VM. I needed to do it through TSQL scripting, which meant I had to learn what scripts to use, but I also had to have a fundamental understanding of what was actually happening to my database. I couldn’t have one without the other.

  • Fourth, get familiar with the Script button in your various SQL Server windows and dialog boxes. Often, when you’re performing some sort of job or task in the SSMS GUI, the software provides a little button up in the toolbar that will show you the actual script that will run once you’ve chosen your options, set various parameters, and hit “OK.”
    Hitting that Script button (when available) before you click “OK” will pop up a new query window in the background and populate it with the script will be run. Use this as a learning tool to see how the choices you made in the task window create specific commands within your script to be executed.
  • Fifth, find yourself some good tutorials. Though it’s pretty basic in a lot of ways—and teaches the ANSI standard alongside TSQL—I’ve found the SQL tutorial at W3 Schools to be an excellent training resource. I’ve spent a few hours using it just this week to reinforce what I already knew and take my TSQL knowledge to the next level.

What have you done to help yourself learn TSQL? I’d love to hear your own tips and tricks so I can try them out for myself.

That’s all for this week. Join me next time for The Ironic DBA Files—Episode 7: The Backup Awakens.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.

The Ironic DBA Files

  • Prequel: The Ironic DBA—Starting a New and Unexpected Career
  • Episode 1: You Back That Up?
  • Episode 2: Attack of the Corruption
  • Episode 3: Revenge of the Index
  • Episode 4: A New Primary Key
  • Episode 5: The Maintenance Plan Strikes Back

Follow @Dallas_DBAs

Filed Under: Apprentice, Beginner, Career, EntryLevel

The Ironic DBA Files—Episode 5: The Maintenance Plan Strikes Back

July 9, 2019 by SQLandMTB Leave a Comment

Welcome back to The Ironic DBA Files, a series where the newbie DBA on staff at Dallas DBAs chronicles the ups and downs of fast-tracking a new career as a DBA.

In the previous post I rambled about for a bit talking about primary keys and clustered index keys. That writeup was very tip-of-the-iceberg stuff and there’s much, much more ground to cover on that topic in the future. This week I circle back around again to touch on a topic I’ve already written about in the past: Maintenance Plans.

You’re Gonna Use That?

I’ve learned that Maintenance Plans have something of a bad rap among experienced DBAs, and I can partially see why. If you’re new to this game like me, let me give you the two main reasons I’ve pinpointed as to why a Senior DBA might tell you to not use Maintenance Plans.

  • Misconfiguration: Like so many others functions within SQL Server, it’s very easy for an Accidental DBA to choose the wrong options and parameters. I’m not going to run through best practices here, but simply knowing that you can screw up your database by incorrectly implementing a Maintenance Plan is something to be aware of.
  • Inferior Product: While SQL Server is itself an enterprise-level piece of software powerful enough to encourage companies and governments worldwide to use it for their data, let’s admit that it’s not the best at everything. When it comes to performing regular maintenance tasks on your server, there are better options out there if you know where to look.

Here’s the core issue with both of the complaints above—an inexperienced DBA doesn’t know any better. And as we’ve discussed before, so many DBAs are not trained, let alone full time. They were thrown into the server room by the boss and told, “fix it!” just because they were the staff computer geek.

If an Accidental DBA digs just a little bit into the workings of SQL Server they’ll discover the Maintenance Plan Wizard and think, “Hey! This is easy!” The problem is that the Wizard assumes you know what every available option does and how it affects your data. They also don’t have the time or experience necessary to understand how to properly implement a third-party set of scripts. Think about it, if a new DBA doesn’t understand how to properly implement a built-in set of maintenance scripts, how can they be expected to implement a more complex third-party product?

Like me, a new DBA is going to look at the Maintenance Plan Wizard their first time out and see the shrink option and mistake it for compression. Compression the database is a good thing, right? So shrinking my database is a great idea! Making a mistake like this can create a database that performs horribly, and the Wizard makes no attempt to prevent you from making poor choices. Even newer versions of SQL Server with an updated Maintenance Plan Wizard don’t help a junior or apprentice DBA understand what the options mean.

Brent Ozar (b|t) said, “If maintenance plans are designed for the accidental and junior DBAs amongst us, I don’t think a lot of this stuff [options included in the Maintenance Plan Wizard] should even be an option. It should just default to the right thing, and take care of the database with Microsoft’s best practices set up as standard.” SQL Server 2016 Maintenance Plan Improvements.

So here’s my attempt to help my fellow apprentice DBAs better understand how to implement a rational set of Maintenance Plans that won’t simply end up making things worse.

Maintenance Plans for Apprentice DBAs

First of all, the best thing you can do for yourself is to go and learn what the various options in the Maintenance Plan Wizard can do, and what implementation of those options will do to your database.

Second, one of the very first things I learned from Kevin is that you should separate all the different Maintenance Plan options you intend to implement into separate tasks. Simply put, while it’s easy within the Wizard to throw together one plan that does an integrity check, an index rebuild, and a full backup, it’s always better to keep them separate and scheduled during appropriate maintenance windows.

Third, related specifically to backups, another guideline Kevin shared early on is that any server in production should be using the FULL recovery model. Why? Because otherwise you can’t perform Transaction Log backups, which means you lose the ability to apply point-in-time restores.

If you don’t know this already, a log backup truncates the transaction log when using either the full or bulk-logged recovery models. A regular log backup helps keep your transaction log from growing uncontrollably, allows you to perform the aforementioned point-in-time restores, and limits data loss during database recovery.

Other quick points:

  • Perform Check Database Integrity Often: Do this DAILY if possible, but at least WEEKLY. Always schedule this during a wide maintenance window if possible. (Read more about CheckDB back in Episode 2.)
  • Learn the Difference Between Rebuild and Reorganize Index: It sounds simple, but this is an instance where you need to understand a bit about what’s going under the hood of SQL Server and how rebuild and reorganize are not the same thing. One of the chief points to understand and learn more about is the fact that rebuilding indexes automatically triggers updating of statistics, while reorganization does not.
  • Create and Schedule Backups to Meet RPO and RTO Standards: If you’re working on a server in production, don’t just throw together a set of backups and call it a day. You need to know what your company/client expects in terms of acceptable data loss and down time. Your set of Maintenance Plan backups must be crafted to fit these expectations.
  • Never Use Shrink Database: NEVER. Perform this manually on demand when necessary. Check out Paul Randal’s (b|t) in-depth look at why you should not shrink your data files.
  • Learn About the Other Tasks: There are three other major tasks you can perform with a Maintenance Plan—Maintenance Cleanup, Execute SQL Server Agent Jobs, and History Cleanup. I won’t cover these here, but go and learn about them once you’ve got a handle on the other steps above.

That’s all for this week. Join me next time for The Ironic DBA Files—Episode 6: Return of the TSQL.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.

The Ironic DBA Files

  • Prequel: The Ironic DBA—Starting a New and Unexpected Career
  • Episode 1: You Back That Up?
  • Episode 2: Attack of the Corruption
  • Episode 3: Revenge of the Index
  • Episode 4: A New Primary Key

Follow @Dallas_DBAs

Filed Under: Apprentice, Beginner, Career, EntryLevel

  • « Go to Previous Page
  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Go to page 4
  • Go to page 5
  • Go to page 6
  • Interim pages omitted …
  • Go to page 12
  • Go to Next Page »

Primary Sidebar

Search

Sign up for blogs, DBA availability and more!

Home Blog About Privacy Policy
  • Home-draft
  • Blog
  • About Us

Copyright © 2025 · WordPress · Log in

 

Loading Comments...