SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Warning: Expect Delays

Imagine you are driving somewhere – to work, to the mall, wherever – and you encounter a massive traffic jam. Wall-to-wall, bumper-to-bumper, you creep and crawl along the highway for a couple of hours, wondering what on earth is going on here, until you finally reach the source of the trouble and discover that the state highway department has decided to begin a major construction project on a critical artery in the middle of the day – and didn’t tell a living soul about it. Nope, they just showed up and started ripping up concrete, putting up cones, detouring traffic. No notice in the newspapers, no announcements on the evening news, no portable signs proclaiming “EXPECT DELAYS”. Just slowdowns, stoppages, and flaring tempers. The outcry and outrage would be tremendous. Someone, eventually, would lose their job over it.

As DBA’s, one of our principal jobs is to keep things moving efficiently. This means doing things like adding new indexes, removing old indexes that aren’t needed or are underused, tweaking procedures, and so on. We can and do work hard to make sure that required maintenance and updates take place when traffic is low, and we make an effort to inform anyone that might be impacted.

That said, it can be difficult to track down all the possible affected parties. You likely have a short list of people to contact before any major work, but is it exhaustive? Probably not. That list may differ from one database to the next; different servers have different constituents. The list will likely change over time, and keeping it updated is a task that’s easily overlooked.

I recently set up a task to move some tables from one very large file into several smaller ones, a task that was going to take a couple of hours and was going to require me to put the database into single-user mode in order not to be blocked by other processes. I sent out an email a week in advance to the usual parties, and hearing no objection, scheduled the task for a weekday evening.

Unbeknownst to me, a team in Europe was working on a project that required several long-running queries. When I put the database into single-user mode with rollback immediate, it cost them several hours of work. By the time I got word from customer support about it, I was too far into the process to stop, and so the team had to simply sit and wait until my work was done.

The next day I set about trying to find out if there was any way I could have forewarned them and given them a chance to ask for a delay in the maintenance work. I had sent out emails to the usual suspects: operations, support, development, etc. I eventually worked out that while I had a short list of project managers to contact, I had not updated that list in some time, and so missed a (fairly) recent hire who was in charge of the project.

To remedy this, I sat down with my boss (and his), and we put together a definitive list of all parties to be contacted when maintenance of this sort was required. We also put in place a process to verify and update that list on a regular basis, so that we would always be on top of changes in personnel and responsibilities.

It’s easy to become complacent about things like this, and to just go with what’s always worked, but sooner or later that will come back to haunt you. Take the time to work with management – and not just IT management – to get a thorough understanding of who knows what about applications, automated processes, and special projects. Your users will thank you.

The Monday Morning DBA

Roland Alexander was an application developer for the first half of his 20+ year career in IT – then he came over to the Dark Side of SQL Server development, and is now diving headfirst into the deep end of the DBA pool. This blog is about his observations, discoveries, trials, and tribulations, and he hopes you enjoy reading it, learn a thing or two, and (hopefully) teach him a thing or two while you’re at it.

Comments

Leave a comment on the original post [themondaymorningdba.wordpress.com, opens in a new window]

Loading comments...