Voice of the DBA

Author: way0utwest

  • 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.

  • A Welcome Intruder

    Have you every tried to get past your own security?

    One of the things that many large companies do is hire companies to evaluate their security. This often involves some sort of test of the security systems by an individual whose experise is breaking into companies. There are some experts who study the techniques used to break security, but I suspect that often former hackers/crackers are hired because they have practical experience breaking into systems.

    However for most companies, the security is only examined when there is an actual issue. I know most IT people that manage web systems are told about security lapses when the site si defaced, or when your data is discovered posted in some other location.

    This Friday I wanted to ask this question:

    How many of you have attempted to penetrate your own systems?

    You could do it yourself or get a friend to try, but have any of you actually performed some type of penetration test and what did you do? I typically haven’t at most of my jobs, but I have spent time thinking about how I would penetrate the systems and then made an effort to close any holes.

    My feeling is that most of the data breaches or losses occur because of attacks against the weakest links in the security system: humans. Social engineering, which taking advantage of most people’s good nature and desire to help others, is usually the biggest problem. Theft of laptops is also an issue, but I think the targeted attacks specifically aimed at your company is fairly rare. The exception would be SQL injection attacks, which spring up constantly at site after site, mostly because of poor development practices.

    We can get better at securing our systems, but it takes some effort, and a belief that we are vulnerable. Maybe setting up a test against your own systems will convince you, or more importantly, your boss, that it is worth the time spent better securing your systems.

    Steve Jones

    The Voice of the DBA Podcasts

  • Fast Migrations

    No matter if Hyper-V or VMWare are faster, competition will spur innovation.

    Virtualization is becoming more and more popular all the time, and if you don’t know much about this technology, I would highly recommend you begin learning. In fact, if you are a technology worker, you ought to have some sort of virtualization software installed on your computer right now. Testing, practicing, learning, all of these are made easier with a virtual machine that allows you to try out ideas without affecting your daily work.

    One of the advantages of virtualized servers is that they can be moved from one set of physical hardware to another. If you find that your virtual machine (VM) is not receiving enough resources from the host, you can move it to another less busy host, and improve performance. What’s even more amazing is that you can move this VM while it is in use. In fact, your clients may not even notice that the VM has migrated to another host, even while connected. To me, that’s absolutely amazing.

    The way it works isn’t important, but how quickly and smoothly it moves is important. VMWare and Hyper-V both support this live movement, and this might be the place we find the next race to speed up performance from software vendors. According to this article, the latest version of VMWare can move hosts five times faster than Hyper-V. Whether that’s true or not, I think it’s good that there is competition in this area, spurring innovation and research into ways that we can improve hypervisor performance.

    Improving that performance will lead to more applications for virtualized machines, including databases. I know plenty of people that are running SQL Server on a virtual host, and doing so quite successfully. It can be done, just be sure you do a little planning and think about how you virtualize your server. Also be aware of the limitations that exist in the hypervisors and don’t assume every SQL Server instance makes a good candidate.

    As always, test before you make the move, and be prepared for issues to come up, but don’t be afraid of making the move to virtualized database servers.

    Steve Jones

    The Voice of the DBA Podcasts

  • A Refreshing Break

    Taking a break on the Winter Park slopes

    Thanksgiving is always the biggest break from work for me each year. My family goes away for a almost a week of skiing to take a break away from our busy lives. Last week I was gone, taking 6 whole days away from work and I came back this week, with a lot of emails, articles, and various other work here at SQLServerCentral.

    This time, however, I tried something completely new. I didn’t check my email from SQLServerCentral, visit the site, or do anything else work related during the time away. That’s a first for me, as the last few years I’ve been pruning emails on my phone during vacation, handling minor items as I had time. It was hard, since my email client would show an ever increasing count of unread messages, but I made a point of not working during the time away.

    I was very refreshed when I came back this week, enjoying a couple days over the weekend of catching up on chores around the house. For the first time in a long time I wasn’t anxious to get back to work, and I completely enjoyed the time away. It never felt like checking a few emails was that distracting, but it was, and it made quite a difference for me.

    It’s the holiday season, work usually slows down, and many people take some time off around the Christmas and New Years holidays. I’d urge you to avoid email and work completely, even on your smartphone when you have a few minutes. Keep a book around, start a few Words with Friends games, or use some other leisure distraction to give you a complete break from work and see if you come back refreshed.

    Steve Jones

    The Voice of the DBA Podcasts