Voice of the DBA

Tag: syndicated

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

  • Forgiving Mistakes

    My main phone line at the house has been on the fritz for about 3-4 weeks. Yes, we still have a main line and it’s a legacy holdover from having that number for a decade and it links do our DSL account. We may get rid of it at some point, but for now we wanted it working. We actually weren’t aware it was down until a service repairperson told us when they arrived. We checked, and sure enough, no dial tone.

    My wife and I are somewhat savvy and we tried to debug it, but it was down. With travel and holidays, it wasn’t until Tuesday morning that we called CenturyLink for service and I was pleasantly surprised by a technician arriving in the early afternoon.

    He tried a few things in the house, then set up a tone generator and drove back out to the street. Yep, he drove. My driveway is 1/4 mile long and it’s another 1/4 mile to the paved road, and a mile and a half to the main road.

    About ten minutes later he came back and said things were fixed. We checked, and sure enough, we had dial tone. He then explained what happened: our line was cut.

    I was surprised and worried, until he explained that when you service lines in the boxes, there is often a little extra wire on a connection. Small scissors are used to snip that off and clean the connections, but there are a lot of wires in there and sometimes one gets accidently cut by a tech. He surmised that another tech was in a hurry and accidently got one of our lines (we have two). He apologized again, we shook hands and went on our ways.

    My Reaction

    I wasn’t quite sure how to react to the situation after that. Should I complain to CentuyLink and seek some credit on our bill? Should I vent on the Internet about shoddy workmanship? In the end, I told my wife it was fixed, explained what happened, and we both let it drop.

    Andy Leonard wrote a great piece on stress during the holidays with a very important section on forgiveness. I forgave CenturyLink because it seemed like the right thing to do. A few dollars on my bill aren’t worth the time it would take me to call and the aggravation I’d cause myself and some customer service representative in order to file a complaint and seek credit.

    I make mistakes at work. Heck, I make them all the time, and I don’t expect to be banned, burned at the stake, or excommunicated for them. They’re mistakes. I learn from them, I try not to make them again, I try to do my best, but I know that there will be a few that I make.

    I expect my co-workers will make mistakes, and I have to live with some of those. I might be annoyed, or inconvenienced, but I understand that most of the time they are just that, mistakes.

    I wonder sometimes if we’ve forgotten how to forgive in this world, forgotten how to tolerate the imperfections that exist in all of us, and in the work we do.

  • Shutting down for a week

    I try to speak and write about career things on a regular basis because I think they’re important topics for those of you working with SQL Server. Arguably they are as important as the technical stuff. One of the things I try to preach is “balance” and making sure that you remember that we work to live, not live to work.

    With that in mind, I’m searching for a bit more balance in my life. This year has involved more speaking and traveling for me, and it was too much. I have been unbalanced the last 60-90 days, but I’ve had commitments to keep and I’ve made them. It’s been hard and now I need to get back in balance.

    One way I’ve wanted to do that is to ensure that I take my vacation each year. I have had a few breaks this year, but not enough, and I’m trying to rectify that. I’m on vacation from Friday, Nov 18, and won’t be back at work until Mon, Nov 28. My family and I are taking a week to ski in the mountains, and I’ll be enjoying a half day at The Woodworking Show Friday before we leave.

    Enjoy your own holiday, stay away from email and work if you don’t have an emergency, and remember to have a good break next week if you get time off for the Thanksgiving holiday.

  • The First Thing To Do After Creating Keys

    I’ve been looking at some security code in preparation for a few talks I plan on doing next year and there’s one thing I don’t see in many of the samples: backups.

    When you get ready to encrypt data in a table, or you enable Transparent Data Encryption (TDE), you will end up creating a key of some sort, or a certificate. Some of these keys, especially the database master key, needs to be backed up. Otherwise you won’t be able to decrypt your database or data later on if you recover this database on another server.

    As soon as you create a key for security, make sure you back it up right then. You might forget later, and then it might be too late. A little preparation will go a long way here.

    The commands are simple, and linked below:

    Securing these keys can be complex, but I would recommend that you place them in an administrative folder with limited access from anyone.