Voice of the DBA

Author: way0utwest

  • Daily Coping 7 Mar 2023

    Today’s coping tip is give positive comments to as many people as possible today.

    On a travel day, I made an effort here. Those are always better for me, when I see a lot of people and can brighten their days.

    • Thanking the bus driver at the airport
    • Greeting the cashier at my favorite restaurant kindly
    • Complementing the flight attendants on their look and service
    • Chatting with the Uber driver and expressing gratitude for recommendations
    • Taking a few minutes with the hotel receptionist
    • Being positive and thanking a waiter at a restaurant

    I started to add a daily coping tip to the SQL Server Central newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

  • Nested Transactions

    One of the very common expectations from many SQL developers involves transactions. Many developers (database or application developers) think they can open a transaction, do something, open an inner transaction (nested), and then commit or rollback the inner transaction separate from the outer one.

    If you’ve worked with explicit transactions and experimented with this a bit, then you know that this doesn’t work. Recently Brent Ozar wrote a post on this as he had a client think that committing the inner transaction would release locks. It doesn’t.

    Knowing whether work gets committed or not is important to data integrity. We often need to ensure that multiple things happen or nothing happens. That’s key, and if we want to decide that thing A can happen without thing B, those are two transactions. In most cases, where we’d want the behavior I described at the top, these don’t need to be nested. They’re just two transactions.

    Understanding how data modifications work is important, especially if you work across different platforms and you need to ensure there is some level of durability. Some platforms use different locking strategies, some limit transactions even more, and digging into the details is important.

    As technical people, we know there are many ways to solve problems, and we often spend a lot of time ensuring that users of our systems have options. We would assume our users will learn and understand how the options work, which is no different that what we ought to do ourselves. Don’t assume. Ensure you know how the database will behave if you depend on it behaving a certain way.

    Steve Jones

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

  • Daily Coping 6 Mar 2023

    Today’s coping tip is to make plans with a friend and catch up with them.

    I decided to reach out to a friend and make time to catch up. This is someone I’ve known a long time and really appreciate in my life, but I let time get away from me and don’t always make time.

    I set up a lunch and we had the chance to catch up on our lives, family, etc. It went well, though a bit short as I had some meetings in the early afternoon. We did, however, set up a second lunch to meet again in a few weeks.

    I started to add a daily coping tip to the SQL Server Central newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

  • The Top SQL Server Engine Errors

    For many of us, SQL Server just works. We might get some syntax errors if we mistype things, but for the most part, SQL Server runs smoothly in many environments. However, there are some common situations that do occur regularly, and I wonder if you can guess which errors often occur?

    I saw a blog this week from the SQL Server Support group where they covered the top 25 errors that come in support calls. Their goal was to see if they could document and help people better solve problems themselves and reduce the support load.

    Can you guess what the first error was? I’m assuming these are in descending order, but that’s not clear. In any case, the top error was #18456, which I didn’t recognize at first. Reading the documentation page shows this is the “login failed for xxx” error, which is probably my most common error. Often because I can’t type a password correctly, but also because of an inability to select the right instance or user name. There are other causes, and it’s nice to see a long list of things people can check.

    The next error was 19407, which is a cluster communication error. If that’s the second most common error, then maybe clusters and AGs need a bit more resiliency or better setup guidance. Third is an OS error with NTFS, which I’ve never run into.

    If you flip through the list, I wonder how many of these errors are common for you. Do they come up often? I know I’ve seen people post on 912, which is an upgrade error and very annoying. I think some of the upgrade scripts for CUs aren’t that well written and should have better error handling inside them. That would seem like an easy one to fix and reduce call volume.

    There are plenty of network errors, including the “error occurred while establishing a connection” one. That one is usually is a typo from me or a misconfiguration of an instance after installation. Lots of other errors seem network or backup related, which may not be common, but those are errors that likely cause people to call Microsoft Support.

    Maybe the most interesting one is 9002, log out of space. While I know lots of people might not know how to manage space, I also see lots of accidental DBAs get caught here because they set up full backups and not log backups. Their databases are small, storage is cheap, and they encounter this a year or so after they’ve set things up. To me, this is really low-hanging fruit by making it really easy to have an automatic backup process added for each database. Just add tooling to help make this easier, or create a job when a new database is created. If this isn’t needed, let it be disabled, but for those that are installing SQL Server for some COTS application, make this a easy.

    A lot of these errors are ones I’d never call support for, but I can imagine others not feeling that way. Plenty of these are errors I’ve never seen, but I’m glad the documentation is more than just a description of what happens. These updated pages give some possible causes and things that the user can do. That’s something all of us would like in documentation when something goes wrong.

    Steve Jones