Voice of the DBA

Category: Blog

  • No Shrink-y Tempdb

    I caught the end of a Twitter conversation the other day between Wes Brown (Blog | @WesBrownSQL), Paul Randal (blog | @PaulRandal), and Aaron Bertrand (blog | @aaronbertrand) talking about shrinking tempdb and the potential issues.

    I’ve never thought about it before, but someone investigated and the question popped up on Twitter. Nioholas Cain (blog | @SirSQL) wrote a nice blog post on the events, and it explains how he came to the question (a full disk).

    The short answer is that you can cause corruption in tempdb if there is activity taking place when you shrink.

    So don’t shrink tempdb!

    At least not without quiescing SQL Server. If you don’t know what that means, don’t shrink tempdb on your active server. In case you don’t know, it means allowing active connections to drain off and not allowing new ones. There’s a KB article on this.

  • The Exceptional DBA for 2011

    Once again we’re running a contest to find the Exceptional DBA around the globe. I’ve been a judge the last three years, and it’s been a hard choice trying to pick from some great applications. This year the judges are Brad McGehee, Brent Ozar, Rodney Landrum and me.

    Nominations are now open, and you can nominate yourself, or a colleague for the change to be named the Exceptional DBA of 2011.

    Best of luck to everyone, and a few hints.

    • More writing is better – We don’t want to read a chapter from War and Peace, but take a few paragraphs to describe your environment with some details.
    • Be specific on the highlights. – Let us know what you did, why it’s important or impressive to your organization.
    • Be well rounded – We are looking for people that do more than their job. They teach others, they are proactive, and they make the DBA profession better for everyone.
    • Proofread – Have a friend or even your boss review the nomination form.

    The nominations are open until June 30, so take a week or so and think about how best to present yourself.

  • Fun with Light Bulbs

    I needed an image of a “great idea”, and decided to use a light bulb. I started looking through Flickr and then realized I could just as easily do my own picture. So I enlisted Kendall for this series of shots.

    fun1fun2fun3fun4fun5fun6fun7

     

    We had fun, with Kendall holding some of them and laughing the whole time. It was a fun silly few minutes in our day.

    I ended up using this one in my editorial:

    idea

  • Collation Conflicts in a SQL Server Join

    I went to run this query recently: 

    select TOP 10 * 
 from users a
   inner join Banned b
   on a.username = b.username

    and got this lovely message.

    collation

    I’d seen that message before, so I knew what was wrong. The collations for the two tables were inconsistent. Since this was a database that was upgraded from another version of SQL, and uses objects from a third party, I wasn’t surprised that a specific collation was used. I had created the “b” table myself, using database defaults, and they didn’t match the object.

    I did a quick search since I couldn’t remember the exact syntax for the clause to add to my query. I ended up at a friend’s blog, Pinal Dave’s SQL Authority, and read this post: Cannot resolve collation conflict for equal to operation.

    The fix is easy, add a COLLATE DATABASE_DEFAULT to the join condition to force a specific collation on the field. I could easily have added a COLLATE Latin1_General_CI_AS as well, but since I knew that the second field was database defaults, I did this:

    select TOP 10 * 
 from users a
   inner join Banned b
   on a.username COLLATE DATABASE_DEFAULT = b.username

    Worked fine, and I was on my way.