Voice of the DBA

Category: Blog

  • MCM Prep – VLF Allocations

    I had heard that you could have too many VLFs in your transaction log, and it could cause performance issues. Kimberly Tripp has a great blog on how to get better performance from your log, but until I was studying for the MCM, I didn’t realize you could have too few.

    The issue seems to be that if you allocate too much transaction log at once, you could end up with a few, very, very large (GB in size) VLFs, and despite regular log backups, you could not be clearing any VLFs inside the log until there are no active transactions in that VLF. And that could cause a performance slowdown as it clears that large VLF.

    How are VLFs allocated? Here’s the formula:

    • chunks less than 64MB = 4 VLFs
    • chunks of 64MB and less than 1GB = 8 VLFs
    • chunks of 1GB and larger = 16 VLFs

    This is from : http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx#ixzz19KysnYcK

    So if you need 50GB of log space, do as Kim recommends and get 512MB chunks by allocating 8GB at a time. You can do this contiguously, as in all the same day, but allocate 8GB, let it finish, then ALTER to add 8GB more, repeat until you reach the correct size.

  • Looking Back at 2010–Goal Review

    I had 8 goals for 2010, which I posted at the end of last year. That reminds me, I need to work on goals for 2011 Smile

    These were a variety of work, community, and professional goals that I set with, thinking that I would be able to achieve these in the year. As with many things, I was wrong and didn’t end up I the place that I expected. Some of this was due to my poor time management, some due to changing situations, and some due to a changing of my feelings towards the end result of these goals. Here are my final comments on the goals:

    1. Forums – My original goal was to post less, but keep 300 posts a month average. About 40-50 of those are set (editorials, handling issues), so it’s really a 200+ technical posting. I managed to make that average, with a wildly varying level per month.
    2. Blogging – I wanted to blog 5 times a week, each week, with at least 2 being technical. I think I made this, outside of vacations, and most weeks I did manage to get a couple technical posts. In some ways, I think this is an artificial goal that forced me to look for technical items, but it also resulted in some less than stellar posts. I think I need to re-examine what I am trying to accomplish here for 2011.
    3. Learn Reporting Services – Fail. I was hoping to devote 1-2 hours a week, a fairly small goal, to this. However besides getting this setup no 3 machines, building some basic reports, and fighting through non-domain security issues, I didn’t accomplish a lot here. Part of the issue is that I had no focus on “what” I wanted to do with SSRS and no work to produce. I think this was my learning goal for SQL Server, but it wasn’t well focused or developed. While I think these are good skills, and worth learning, it wasn’t a good choice for me. In short, a bad goal to pick, and one that was easy to let go as my life became busy.
    4. Attend one SQL Saturday – A huge success, and I actually attended 7, along with a couple other events. I spoke at:
      1. SQL Saturday #33 – Charlotte
      2. SQL Saturday #22 – Pensacola
      3. SQL Saturday #28 – Baton Rouge
      4. SQL Saturday  #52 – Colorado
      5. SQL Saturday #53 – Kansas City
      6. SQL Saturday #59 – New York City
    5. Write a book – I made progress here, devoting a few afternoons here and actually working on two separate items at the same time. However what ended up killing me here is no enough of a focused effort, enough times. If I had spent 15-20 days on this, it would be done. I think that I moved some of this time to speaking events, and building presentations.
    6. Build 2 new presentations – I actually built 3 this year. Two on basic SQL Server (one non-technical, one technical), and one on Common SQL Server Mistakes. I have another in progress that I want to get ready for 2011. Again, need to make focused time.
    7. Take all my vacation in 2010 – Close. I didn’t make it, and again, work is interfering with my ski-Thursdays this winter. However I did manage to get in 4 vacations this year: St Martin, Las Vegas, Winter Park, and Steamboat Springs. I’ll call this a success, assuming I can free up 3 days each month in Jan/Feb/Mar.
    8. Travel to one new city for work – Charlotte, Kansas City, Las Vegas, New York (for SSC) were new trips for me. It was good to me to visit with people outside of the areas I have tended to go (Seattle/Orlando) over the years.

    So was it a successful year? It’s hard to say. It certainly was a busy one, and that impacted some of my personal goals. I think that overall I did well, and some of my failings were a lack of a well-focused goal.

    In 2011, I want to look for goals that produce some result, and not arbitrary goals like blog every day. I am looking to better define a goal that will make my career grow, as well as contribute in a positive way to the community for SQL Server.

  • Getting a Dedicated Admin Connection

    Did you know you can easily get a Dedicated Admin Connection (DAC) in SSMS? I didn’t assuming that I’d need to use a command line and SQLCMD. However while studying for the MCM, I learned that there’s an easy way.

    I tested this on a SQL Server 2008 instance, having a normal connection, as seen in the lower status bar:

    admin_connection2

    Right click in the query window, and select “Change Connection”. In the dialog, not add “Admin:” before the server name, as shown below.

    admin_connection

    When you connect, you’ll have an admin connection, which you can see in the status bar.

    admin_connection1 

    Pretty cool.

  • Rebuilding my laptop

    I finally got around to adding the new memory (16GB), removing the old 4GB and then replacing the DVD drive with an SSD. Now he hard part: rebuilding the machine.

    In order to use the 16GB, and get VMs to run more smoothly, I need 64 bit Windows 7. I had left the x86 install when I received it since I was worried about my Google calendar sync, and at the time I had a few 32 bit programs that didn’t work well with my 64 bit desktop.

    Almost a year later things are fairly smooth on the desktop, and since I plan on doing more presenting, and more VM work, I need to take advantage of the RAM. Which means a rebuild.

    First step is to back up my machine, which I’m doing now using my Windows Home Server. The next step is to then clone my drive to be safe, and finally I’ll do a reinstall of Windows and begin setting everything back up.