Voice of the DBA

Tag: administration

  • Autogrow guidelines

    I always enable autogrow on my databases. However, it’s there for emergencies, not as a space management tool. I monitor disk space, and I grow my files manually as space runs low. I want to control the growth, but in the event of a runaway process or some unexpected event, I want autogrow enabled to hopefully prevent a database crash.

    What level of autogrow do you enable? That was a question I saw recently and it made me stop and think a bit. I asked the question on Twitter, but got very few responses and no real guidance from others.

    In my mind, you want to enable a specific level of autogrow that will be likely to handle something unexpected in a single, or maybe a couple growths.

    The two options for autogrowth are:

    • percentage
    • fixed size growth

    In general I think a fixed size growth is the setting to choose. This offers more control and as your database size grows, it’s unlikely you want to grow at a percentage. A 10% growth of a 100MB database is 10MB, almost a rounding error on many of today’s drives.

    However a 10% growth on a 2TB database is 200GB, which could easily exceed the free space on the drives that make up a file,which is where you set the autogrow specifications:

    autogrow1

    As the file size increases, the disk space goes down, but the percentage growth goes up as well, exacerbating the problem.

    So what are the guidelines?

    I think that you have to look at the data growth and each database and what is possible for data growth in that database. Some some database that does ETL work I think it’s more likely you could have an unexpected large or duplicate import in a process that would cause larger growth.

    However the question I was asked talked about setting a guideline for hundreds of databases, where in the short term it’s impractical to review every database. For those, I offer up these guidelines, though I’m happy to have someone give better guidance.

    MDF Size File Autogrowth
    < 1GB 100MB
    1GB < mdf size < 50GB 2GB
    50GB < mdf size < 200GB 5GB
    200GB < mdf < 1TB 10GB
    > 1TB 50GB

     

    These are guesses on my part, based on some experience, but the largest database I ever managed was a 600GB one, so I’m guessing on the TB scale.

    I’d welcome your comments and experience in this area.

  • Recovery Models

    In SQL Server we have three basic recovery models: full, bulk-logged, and simple. By default we find that most databases use the defaults, which mean that they are in the full recovery model.

    In that case, you need to be sure that you are performing log backups, otherwise the log will grow until it reaches it’s limits, or you run out of disk space on that drive. If the log cannot record SQL Server transactions, the database cannot accept any more transactions.

    The basics of recovery models are covered nicely in this article from Gail Shaw, which includes some common myths and misconceptions out there. However for the average person, the important thing is that you understand which recovery model to pick.

    You Need Point in Time Recovery

    Point in time recovery means recovery in between the full or differential backups. Quite a few DBAs will ask customers if they really need to recover to a point in time, and get the answer that they don’t, but that’s not often the right question to ask.

    Ask your clients if the database failed at 5:00pm today, and all the work done today was lost because you restored to last night’s backup at midnight, how would they feel?

    Sometimes they’re fine with the data loss, most times they aren’t. If you need to get back to a point in time between backups, make sure you use the full recovery model.

    You Can Reload the Database

    There are some databases, usually data warehouses, that can be rebuilt from other sources. If you take a backup of your database and then load data every day that rarely changes during the day, you might not need point in time recovery. In fact, many ETL processes are not designed for this anyway, and could not restart themselves in the middle of a load if you restored to the point in time when the database had an issue.

    In this case, use the Simple recovery model.

    You are space constrained with the log

    If you run index rebuilds, or large data loads and find yourself with a transaction log that grows very large, you might want to investigate the Bulk-logged recovery model. This model is more confusing, so I don’t want to give you a general rule here. If you think you might benefit from less logging, investigate the bulk-logged recovery model, practice restores with it and make sure you fully understand the implications of using it before you set a database in this mode.

  • The Robot DBA

    Manna, a fun, and scary, sci-fi story.

    This is the story of Manna, the manager of a fast food restaurant. It’s a great read, and perhaps a little shocking. Manna is a piece of software, reading sensors, reacting, and letting employees know what to do with a synthesized voice through a set of headphones that the everyone wears in a fast good restaurant. In this tale, Manna evolves into a software system that is spread throughout many industries, essentially displacing managers and many workers at all levels in all industries. With the ability of these different “Manna” variants to communicate and negotiate contracts among themselves, the US devolves into an Orwellian nightmare place to live.

    It’s a science fiction story, but the fears that it evokes are similar to those I’ve heard expressed from many people in the IT field across my two decades in this industry. A friend of mine left IT a decade ago, while working as an Exchange admin for a company with 50,000 mailboxes. He was sure in 5 years his job would be automated and eliminated. I knew a few people that moved out of DBA work in the early 2000s, after Microsoft released SQL Server 7 and 2000, with some marketing messages that the products didn’t require a DBA. A few years ago I heard a few people arguing that PowerPivot would kill most BI development jobs because it was so easy for end users to build their own analysis applications.

    The one constant in all of those stories is that they never came true. I hope that Manna never comes true either, but I’m not worried. I’m very confident that if there’s one truth to all the computer development we do, it’s that we make constant mistakes and there is very little chance that we will be able to automate any significant percentage of IT jobs away.

    We do build better software, and we do allow an administrator to manage many more systems than in the past, but we also seem to constantly eliminate most of the efficiencies in two ways. One is by adding many more systems and applications, and the other is by introducing more complex systems that create new problems that need to be solved.

    The bar for IT knowledge needed is always being raised, but I just can’t see us getting to the point of having automated or robot DBAs running SQL Server in my lifetime.

    Steve Jones

    The Voice of the DBA Podcasts

  • Measurements

    Measurements are important to growth.

    “If you cannot measure it, you cannot improve it” – Lord Kelvin.

    That’s a quote that comes from this blog post, talking about ways in which you might measure your performance. Numbers are not the only important thing to measure, and you certainly don’t want to just pick one measure. The blog post talks about performance of employees, looking at different ways to not only measure the performance of one job, but also using different measures for different jobs.

    The idea of measurement is applicable to both people and systems. Having a constant way to measure performance, track those measurements, and perform analysis of the data is an important way to know if you are actually making strides toward building better performance and more efficient systems.

    However it seems that even though many people know these things, they don’t actually capture the measurements regularly. As I talk to people, asking them if they know things like the backup growth for their critical databases, they don’t. I’ve asked people if they know how much time they spend on blogging, or if they are reaching more people, or really for any metrics, and they often don’t have any idea.

    Numbers aren’t everything, but they ought to be a thumb-in-the-air measurement that guides you to find ways to improve over time. You can be on the schedule that works in your environment, and on the pace to improve things that fits the situation, but if you don’t measure yourself or your systems regularly, it’s hard to know if you’re actually moving forward or backward.

    Steve Jones

    The Voice of the DBA Podcasts