Voice of the DBA

Author: way0utwest

  • A New Word: Moledro

    moledro – n. a feeling of resonant connection with an author or artist you’ll never meet, who many have lived centuries ago and thousands of miles away but can still get inside your head and leave behind morsels of their experience, like the little piles of stones left by hikers to mark a hidden path through unfamiliar territory.

    Moledro is something I get mostly from music. I’m less interested or thoughtful about most visual arts, though perhaps media counts. In any case, the poetry of some artists creates a connection with me that sticks with me.

    A few examples:

    • lately, Zach Bryan. his poems and songs connect with a younger version of my, full of hope, heartbreak, and silly youthful decisions.
    • Bob Dylan, wanting a better world, and living a life that he enjoys.
    • Prince, a man truly wanting to entertain and make music, but his own way. In many ways, we’re very different in how we approach life, but I feel this connection with someone that live his life as his career.
    • Steven King – I wish I could write like him, or have the drive to do so, but I love his activism and opinions.

    There are likely many more, but these stand out to me.

    From the Dictionary of Obscure Sorrows

  • Enabling an Index: #SQLNewBlogger

    I don’t do a lot of work with disabled index, but I learned how to re-enable one today, which was a surprise to me. This short post covers how this works.

    The Scenario

    Imagine that you have an index on a table. In my case, I created this index:

    CREATE INDEX LoggerNCI ON dbo.Logger (LogID)

    I can then disable this index with the following code:

    ALTER INDEX LoggerNCI ON dbo.Logger DISABLE

    I had assumed that ENABLE would be the opposite, but SQL Prompt taught me this wasn’t an option. I checked the docs, and sure enough, it’s not ENABLE.

    It’s resume. This code turns the index back on and updates it.

    ALTER INDEX LoggerCI ON dbo.Logger REBUILD

    I can also use either of these items:

    CREATE INDEX LoggerNCI ON dbo.Logger (logid) WITH DROP_EXISTING

DBCC DBREINDEX(Logger, LoggerNCI)

    Interesting short moment the other day as I realized there are a few options here.

    SQL New Blogger

    While playing with this, I realized that I didn’t know all the ways this worked, so I spent 10 minutes after I’d worked with the code to put this together.

    A nice short way to showcase some learning.

  • Cleaning Up Your Database

    How many of you have objects in your database that aren’t being used? What about something in a schema with a _old in the name? Or _2 or _3 or _delete? There is a lot of old, deprecated stuff I see in production databases. In fact, I’ve been somewhat amazed as I work with clients that many of the scripts we can build from a database with SQL Compare won’t actually execute on an empty database because the script is full of broken code.

    I also find plenty of DBAs that want to clean things up, but they don’t. Sometimes they’re afraid they’ll break something, which is certainly possible. Sometimes they can never find the time. Often they might ask a manager, who usually says this isn’t important and don’t bother.

    Is it worth it to clean up your databases?

    Brent says no for old code. I say maybe for tables and code.

    For a lot of code, Brent is right, your boss doesn’t care and it doesn’t necessarily help you. After all, it’s in production now, and if it’s being used, you’re going to just create problems with a DROP. Where is the business value for removing old code (assuming it isn’t being used)? What benefits do your clients get? Not you being happier there are less objects, but what is the business benefit.

    That’s the key. Is there a business benefit.  What I’d say is that if you have broken code, it needs to be removed. Because this does impact your software development process, especially when trying to match lower environments. For broken stuff, save the code in your VCS (you do version control database code, right?) and then delete this stuff from prod. It’s broken.

    Or fix it.

    For tables, I would want to get rid of old tables as well. Why? Well, this is real costs in storage and potential reading of old data. If we moved data to table_old and someone decided they needed to read this for a report at the time, they might still be reading old data. I’d first rename these objects as object_delete_date with the date being a month away. Then I’d set a reminder for that date. On that date, bcp out the data, then drop the table. Period.

    Two other things. First, make sure you know how to recreate the table (see the VCS comment above) and bcp in the data. Two, this is low-priority work. If you want to clean the database, know this is a long term, baby step process that will take months or years, and may never end.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • Back to Boston for SQL Saturday

    I’m leaving again tomorrow for a trip. This time I head back to Boston for a Redgate DevOps in a Day on Thursday and SQL Saturday Boston 2024 on Saturday. This is a fun event and one I’ve attended the last few years. It’s one I look forward to as well.

    If you’re in the area, register and join me Saturday for a packed schedule of learning on PoSh, Fabric, TempDB, AI, and more. If you want something a little lighter, I’m doing a new session on balancing life with career.

    And if you’re coming Saturday, what about coming Friday for a pre-con with Bob Ward with a Cloud Workshop. A good, inexpensive way to get some training from one of the top technical engineers at Microsoft.

    Come join me and invest in yourself and your career at SQL Saturday Boston 2024. And if you want, come Thursday as well to our Redgate Database DevOps in a Day.