Art of the DBA Rotating Header Image

November, 2011:

Brushing up on your (backup) history

Nov 14th, 2011
by Mike Fal.
2 comments

Backups are huge, right? As administrators, they should never be far from our minds. Backups are one of the cornerstones of disaster recovery and safeguarding our data. It only makes sense that we want to keep an eye on when backups happen and if they’re successful. Recently, I’ve been making a lot of use of a simple little query that shows me how my backups are doing:

SELECT
  database_name,
  type,
  max(backup_start_date) last_backup
FROM
  msdb.dbo.backupset
GROUP BY
  database_name,
  type

Yes, this is a VERY straightforward query, no hidden magic here. But with this one little SELECT statement, I have been able to quickly establish if my backups are getting run, what specific databases may have problems, and where I need to focus my troubleshooting. By breaking it down by database and type, I am also able determine whether or not my log backups are running (key to out of control log growth), if I’m properly maintaining my recovery chain, and what backups are going to be necessary to restore my database.

Unlocking the secrets

The key is the the backupset table in msdb. This table stores the history of all successful backup operations on the server(emphasis on successful). It’s not just native SQL backups, but those by third party tools as well, so we can have insight in to any backups (or lack of) that are happening. I first came across this table working with Brent Ozar’s(b|t) Blitz script. Brent introduces this query as part of his server take over to establish whether or not your backups are being taken, which is the primary reason I check it. Backing up our data is vital and not doing this would probably be an RGE. If nothing else, this table says “Hey dummy, I need you to look at something!”

If we dig in a little more, though, we can find other useful information. Take the backup_start_date and backup_finish_date fields. By using another simple query, we can then see how our backups perform over time:

SELECT
  database_name,
  backup_start_date,
  datediff(mi,backup_start_date,backup_finish_date) backup_duration,
  backup_size/(datediff(mi,backup_start_date,backup_finish_date)+1) bytes_per_minute
FROM
  msdb.dbo.backupset

Backup duration is a handy metric for base-lining our systems. After all, if our backups start taking longer and longer, it could be an indication of resource contention. If we’re backing up to local disk, we could be choking our throughput. If we’re backing up to a network share, we could be seeing increased bandwidth usage. Also, our databases could just be getting bigger and might require more resources to support them. By including the backup_size field, we can see if longer backups are the result of simply backing up more data or if there might be something else to it.

If we use the backup_size field and its sibling, the compressed_backup_size field, we can gain some additional insights. Since backups will grow in relation to how much the database grows, this gives us a quick and dirty way to report on those growth patterns. Ideally, you’d want to be track the actual database size, but if you don’t have something to capture that historically, this will work in a pinch. Additionally, by combining it with the compressed backup size, you can get an idea of how much space you’re saving if you use the WITH COMPRESSION option in SQL 2008. A quick gotcha here: This will not show the benefits of compression using third party backup tools. Both fields will show the fully compressed backup size if you’re using something like Red Gate SQL Backup, Quest LiteSpeed, or Idera’s SQL Safe.

Cleaning up after yourself

Now not everything with this table is sunshine and rainbows. Maintaining this history is one of the more commonly overlooked areas of SQL Server administration. Ever wonder why your msdb database keeps growing and growing over time? Bingo, it’s because your backupset table keeps increasing with all the additional history stored inside it. What you want to do is schedule a regular run of sp_delete_backuphistory to keep your history to a relevant period. What that time frame is depends on your database size and needs, though I personally go with 90 days as my default.

Those who don’t learn from history…

Successful database administrators are the ones who make monitoring a part of their practice. With it, we can be proactive with our systems and solve problems before they occur. The great thing about the backupset table is that it’s a very easy way to start checking up on your instances, leveraging information that is already a part of the SQL Server core functionality. You don’t need any fancy tools or complex systems, just a few basic queries to get started understanding how your servers, databases, and systems perform on a day to day basis.

In the land of Jazz and Meat

Nov 3rd, 2011
by Mike Fal.
1 comment

I’ve been on the SQL Saturday kick lately.  Truly, these are great events that really tell you what the SQL community is all about, even more so than the Summit.  This time I was at SQL Saturday Kansas City, getting my community involvement on.  The best thing about these events is you get to interact with folks who are getting their first taste of the SQL community, which gets me really excited to share and enjoy.

I was tapped to present on two sessions, my SQL Server Security session (which I’ve done several times now) and my new Query Plan Primer session, where I do a basic rundown of reading query plans and some of the operators we can find within.  Both went well, though I’ve got my presentations lined up to be ~60 minutes each and the KC folks slotted the sessions to be 75 minutes.  This worked out well becuase I got into some good Q & A time with the audience in the remaining time.

I’m getting more and more comfortable with the whole presentation thing. It’s a ton of fun and really stretches my learning.  As I’ve told many people, the act of building a presentation really drives you, getting you to study up on the subject matter.  Open Q & A time is also a bonus, because while you can read all that material and build a nice little presentation, having 30 people quiz you on it is even better because it forces you to think about the subject from different angles.

I also attended a couple sessions that were good.  But it worked in Dev! with the illustrious Randy Knight(b|t) was great fun.  Randy’s a heck of a speaker and was able to express some of the cardinal development sins we find in our production environments in a straightforward, understandable way.  His presentation is that kind that needs to be given at any shop with a serious developer presence.  I caught a solid powershell session by Mike Lynn(t) that was a good introduction to the fundamentals of the language.  Finally, I learned about service broker from Sanil Mhatre(b|t).  Service broker is one of those cool solutions that has a lot to offer, but it’s hard to find the right problem for it to solve.

The KC group did a solid job with the event, from a comfortable speaker dinner, to an excellent event venue, and wrapped it all up with smooth exection.  Shout outs to Bill Graziano(b|t), Bill Fellows(b|t), Kris Nessa(b|t), Andy Cross, and everyone I couldn’t remember.  You guys have got this <<redacted>> DOWN.  I’ll definitely try to be out for next year.

A month of #sqlawesomesauce

Nov 2nd, 2011
by Mike Fal.
1 comment

Woof.  Can’t believe I got through October.  No, scratch that.  I can’t believe I scheduled so MUCH for October.  Really, I should know better, but when life hands you a plate of coolness, you dive in with both hands.  Yeah, we’ve all had those times when we go a little overboard on the things we like and end up paying for it, but is it ever not worth it???

So what happened?  Well, I had two SQL events this month that I thoroughly enjoyed.  In reverse order, I did two presentations and enjoyed awesome barbecue at SQL Saturday 101.  But before that, I made it to the SQL PASS Summit.  There’s no way I can properly convey how much fun this all was, but I’ll give you guys the quick hits as best I can.  I’m going to break the wrap ups into a two parter, just because I talk to much to assault you with THAT wall of text.

Climbing the Summit

So I finally made it.  The PASS Summit.  The big hoop-de-doo.  Was it worth it?  HELLS YES.  By the end, I was felt like my brain and body had been put through a meat grinder, but I knew more and was ready to do more when I got back to my job on Monday.

Before we start talking about the details, I want to impress upon you almost how overwhelming the summit is.  There’s so much going on that if/when you go, you’re going to be running non-stop for the hours you are awake.  Starting at breakfast, then the key notes, then the sessions, then the lunch, then more sessions, then the after parties…..woof.  It’s crazier than an unhappy on-call week.  But believe me when I say it’s absolutely worthwhile.

Sessions

Tagging along with the previous comment, you may look at the schedule and want to attend something in each time slot,but it ain’t gonna happen.  There’s to much other stuff going on outside of the presentations for you to make everything.  I tried to make about 75% of the time slots.  The highlights were:

  • Internals of TempDB with Bob Ward – This session about broke my brain, but it was a good pain.  Bob really drilled into what goes on in TempDB.  While the information may not have a direct impact on my day to day work, it was still plenty valuable.
  • Bug or Feature with Itzik Ben-Gan – Man, Ben-Gan is an AWESOME speaker.  Funny, clear, and concise.  I hope he does a pre-con next year because I absolutely want to hear him for more than just an hour.  This particular session was a good insight in to how T-SQL is interpreted by the engine and covers a lot of those odd situations you might see with odd code.
  • Are you a linchpin? with the superstars – This was a panel discussion on professional development by all the big names:  LaRock, Ozar, Misner, and others.  It was a nice back and forth discussion about taking charge of your career.  I especially liked Kevin Kline commenting on “what adjectives do you want people to describe you with”, which got me thinking about many things.  More on that later.

The biggest problem with the sessions is that there are so many great ones to choose from, so you’ll definitely want to get the DVDs after all is said and done.  Pick the ones you REALLY want to go to and make sure you leave some downtime in between.

Keynotes

This was mostly a disappointment for me.  The first day’s keynote was a big letdown with product announcements.  I know everyone was hoping for a concrete Denali (now 2012) release date, and unfortunately when that didn’t come everything else seemed weak.  Some of the Hadoop stuff was mildly interesting, but I would have been a lot more interested if I had first seen the Dewitt keynote which really explained just what the big deal with Hadoop was.  Much of the BI/reporting stuff was lost on me.  So much so that I decided to sleep in a little the next day and skip the Thursday keynote.

Friday was a different story.  I had been hearing everyone talk about how awesome Dewitt was and I was looking forward to it, but I was also wondering if anything could live up to the hype.  Fear not, gentle reader, it lived up and then some.  Dewitt spoke on Big Data, explaining NoSQL databases and how they matched up to the traditional relational database systems.  Overall, it was very enlightening.  I went in sort of knowing what NoSQL was, I came out having no questions about it.  Very eye opening and I would recommend the keynote to anyone wondering what NoSQL was and how it does its work.

Networking

The parties and socializing were mostly hit with a couple misses.  On Monday, the event at Lowell’s was a great start to the week because I was able to reconnect with many of my SQL friends nationwide.  Tuesday was a mix, because the first timers stuff was very well done.  The organizers really went out of their way to welcome in the new class.  I got to meet many of my personal SQL heroes, mostly through random chance.  Of course, what I love about the SQL community is that every person was so approachable.  I’ve yet to encounter elitism at any event.

Wednesday night and Thursday night was nothing truly official, just hanging out with different groups of folks.  There were only a couple real regrets:

  •  Not getting out to hang with the SQL Karaoke crowd.
  •  Not shooting better pool at the Tap House.
  •  To many friends to hang out with, not enough time.

Odds and Ends

Here’s a few random hits from the week of what worked and didn’t work:

  • Using a tablet for notes as opposed to paper or a PC was a big win.  Lugging a PC would have been a big hassle and the tablet really gave me good consolidation of my notes electronically.
  •  Bring business cards!  I didn’t hand out that many, but they are a huge networking tool!
  •  Next year, I need to leave on Saturday.  Leaving Friday night was a drag socially and physically.
  •  Make sure to plan a day ahead of the conference to do some touristy things.  Seattle is a great town, get a chance to experience some of it and don’t wrap everything up in the conference.

Maybe size is everything!

I just want to get this across:  The summit is huge!  Just like it can’t be described in one or even two blog posts, it’s almost more than one person can attend.  It’s enriching and a great experience, but you really have to pace yourself and realize that you’re not going to be able to get to everything.  And that’s fine, because everything you do go to will benefit you in some way.

Oh, and one last thing.  I know a lot of people struggle with paying for this and trying to talk their companies into sending them.  I have the same problem.  In fact, so much so that I sent myself.  That’s right, I was totally self funded.  That meant I had to be careful on some stuff and not go to pre-cons like I wanted to, but trust me when I say I got my money’s worth.  I want my company to send me since it’s to their benefit as well, but when everything is said and done, this is another investment in my career that I am happy and willing to make.

See you next year!