Voice of the DBA

Category: Blog

  • SQL Server 2008 – CDC Retention

    I had done a little work with CDC last year, experimenting with the way that it handles changes in your database. Someone had asked me the question about the retention period since the default of 3 days was not sufficient for their environment.

    BOL lists the default retention period as three days, and this is based on a cleanup job that goes through and removes data from the tables. However you can alter that with a stored procedure: sys.sp_cdc_change_job.

    There are a number of parameters for this procedure, most dealing with managing the load of the cdc jobs. You can alter the cleanup or capture jobs with this, but for the retention period, you want to change the cleanup job.

    @retention is the parameter that indicates the number of minutes that change rows are going to be retained in the CDC tables. If you pass in a NULL, the old retention period is retained. You can include the number of minutes you’d like, up to 52494800 (100 years). I would recommend you choose something less.

    The other thing to be aware of is that you have various intervals that are set, and also a @threshold, which determines how many rows are deleted on each scan. If you limit the cleanup to something less than continuously operating, then be aware that if @threshold is too low, it might not delete all the rows. If you are changing these values to manage the load of deletions, be sure that you are monitoring how many rows are changing as it might grow over time.

  • T-SQL Tuesday Topics

    I was asked to host a T-SQL Tuesday, and I accepted.

    Once again, my fingers move a bit faster than my brain. “Sure, be happy to host one. Send me a date” flew out of my fingers before I thought through the implications. I’m happy to help the community, and I think Adam Machanic (Blog|Twitter) had a fantastic idea with the T-SQL Tuesday blog party, and I look forward to them every month.

    But I’m busy, and this is a very busy time of year for the next month, with the hope that most of December will be quiet for me. It’s been a crazy year for me and I’m looking to try and better manage life next year.
    Adam immediately responded to my email with a date, and I have December. I was thinking he’s a busy guy, and my mail might get lost in the shuffle. However it didn’t and I ended up with not only an acceptance, but a date that’s not too far in the future.

    So I need a topic.

    There are any number of topics out there, and after all, T-SQL Tuesday has only been around for a year. However I was somewhat stumped as to what a “good topic” will be. After all, I want this to be a good one.
    The ones I’ve seen so far are:

    I’ve got a few ideas, and some work ahead of me to develop something in the next month around SQL Connections, SQL Saturday #59 and a week of family vacation. If you have any ideas of your own, don’t post them. Save them for your own T-SQL Tuesday and contact Adam to host one.

    I’m not sure what will happen in November, and I’ll be jammed up with a post since I’ll be at Connections next week, so whether it’s the first Tuesday or second, I’ll be hustling a bit to participate.

  • New Looks

    It’s been quite a few years since I updated the Hawaiian shirt wardrobe. I bought a series of 5 shirts for TechEd years ago, having “SQLServerCentral.com” embroidered on each of them. I had 3 copies of each shirt, with Andy, Brian, and myself having a selection of shirts to wear.

    In the in between years I’ve purchased a couple more, but no customization. However this year I needed to outfit my “Scary DBA bouncer” for the SQLServerCentral party. He made a joke about getting a shirt like the one I wore for the Baton Rouge SQL Saturday keynote, and I decided it was time to get a few more for myself, so I started searching around.

    The last time I was looking for someone that would customize the shirts. I didn’t find a lot of places in 2004, but there are more now. However I was a little worried about turnaround. So I looked locally. After pinging 3 or 4 places in the Denver area, Burbank Embroidery said they’d be able to handle a few pieces (I was thinking 6 or 7 shirts) at a reasonable rate and have them done by Oct 29th.

    So then I was searching for shirts. For some reason I wandered onto Beanteacher Hawaiian Wear and found some great shirts. They had a nice online store, and when one of my shirts was out of stock, they promptly emailed me with a few choices and I picked one. My order arrived within a week, and I was happy with the qualify and service from them.

    The shirts are at the shop, and I should pick them up next week sometime, just in time for them to accompany me to SQL Connections in November. And in time to get the Scary DBA properly dressed.

  • SQL Saturday Advice – Extra Food

    No matter whether you provide the food or have people pay for it, it seems that at every SQL Saturday I’ve attended, there is extra food left over. Sometimes it’s a very significant quantity. At one of the events I think there were over 50 boxes lunches left over.

    You need a disposal plan.

    Now it’s easy to toss the food, or give some to volunteers if they want it, however I’d encourage you to think a little wider, or maybe even more socially. Think about how you put that food to good use.

    At the Rocky Mountain Tech Trifecta that we’ve run for a few years we typically have no issues with leftover pizza. Plenty of students are wandering the halls on a Saturday and are more than happy to help us dispose of a few slices. I like to think we’re doing a good thing by allowing students a free meal after the attendees have eaten.

    In many events, however, there isn’t a large group of people walking around the area that would appreciate the food. After both the Denver and Kansas City SQL Saturdays, the leftover food was donated to local missions that used it to feed those people who are down on their luck. I know a few other events have done this as well, and I’d encourage everyone to consider a local mission in your area. If you are having trouble finding one, contact a few churches nearby your event and I’m sure they can help.

    I’d like to think that those of us in IT are fairly well off compared to many people in the world. And I think that we can do a bit more to give back to others that might not be as fortunate. Have a disposal plan for extra food and don’t let it go to waste.