Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #187–Solving Problems

    This month we have a great invite from Joe Fleming, a first time host of T-SQL Tuesday. Joe reached out when I requested some hosts and I’m glad he did. He’s got a great challenge for people and I’ll answer in two ways, for work and non-work.

    I manage the T-SQL Tuesday site and I’m always looking for hosts, so if you want to host one month from your blog, send me a note on Twitter, Blue Sky, or at SQL Server Central.

    Troubleshooting SQL Server

    In my career, I’ve had all sorts of issues come up. Here’s a description of one of the stranger issues. This isn’t the exact issue, but it was similar to this.

    A user reported they couldn’t log into the server. They were logged on earlier, but can’t connect now through an application. What could be the problem?

    When looking at this type of issue, I usually think there is some sort of network issue here, or perhaps a service issue. My  thoughts are:

    • Can I connect? Or can others? (trying to determine if it’s this user)
    • Can this user connect with another app that might give an error message?
    • Is the server instance up? (check the basics and isolate if this is networking)
    • Has this login had a change to permissions/password/etc. Perhaps an AD change.
    • Has something else changed on the server?
    • Has something changed on this user’s machine?

    At some point, I’ll isolate where the issue is and determine how to fix it. In this case, I managed to determine the user was a DBA that was playing with a logon trigger and locked themselves out.

    The UTV Won’t Move

    On the ranch we have to learn to handle lots of things ourselves. YouTube has been a boon, but it’s also a bit of common sense and problem solving that we need to get things done. It can be hard to get people out to the ranch, especially for small things.

    Sometimes big things.

    Awhile back I came back home from a trip to find the spare UTV we have in one of the fields. I asked what had happened and a kid said that it died.

    That’s not a good description, so I asked how it died. What was going on? what was happening? This kid said they’d stopped the vehicle to do something and put it park. When they went to shift into drive, it wouldn’t move.

    Did the engine run?

    Yes, but the UTV didn’t move.

    Now that I had a better story, I could debug further. I knew that it ran and went out to start it myself. Sure enough, it starts and the shifter didn’t do anything, but it felt loose.

    Sometimes hands-on helps. I knew immediately a cable had broken.

    First thing, get this out of the field. I knew there was a way to shift it without the cable, I just had to figure out. (YouTube to the rescue). Once I did that, it’s research to figure out how hard this is to replace and where I can get the part.

    I logically move through the steps of how can I practically get things done.

    From here, I saw someone talk about this on YT and show me this isn’t hard. I learned how to shift into Drive with a pair of vice grips, got it up to the house, and I found the part online. I ordered it, send the YT link to the kids, and tasked them with fixing the cable.

    They did, after some problem solving from me.

  • T-SQL Tuesday #186–Agent Jobs

    It’s that time of the month again, when the T-SQL Tuesday blog party takes place. I manage this site, and am looking for hosts all the time. This month I managed to convince Andy Levy to host, and I’m grateful for his participation.

    His invitation is asking about SQL Agent jobs and how they are managed. It’s focused, but he gives a lot of choices for how to examine this subsystem in SQL Server.

    Note: if you work in Oracle or PostgreSQL or anything else, how do you schedule work in an automated fashion? Cron? Something else? You can still write.

    If you want to host, ping me and I’ll get you a month.

    Designing Jobs for an Enterprise

    I used to work in a fairly large enterprise (5,000+ people, 500+ production SQL instances) with a small staff. It was 2-3 of us to manage all these systems, as well as respond to questions/queries/issues with dev/test systems. As a result, we depended heavily on SQL Agent.

    We decided on a few principles which helped us manage jobs, with a (slow) refactoring of the existing jobs people randomly created with no standards. A few of the things we did are listed below. This isn’t exhaustive, but these are the main things I remember.

    Name schedules clearly

    Scheduling gets crazy. As a result, we would try to name with the days and times something ran. For days, we’d use SMTWRFSa. If something ran every day, that was in the name. If it were week days, then it had MTWRF in the name. Thursdays only were R.

    We’d include a time, such as 0200 or 1830 in there. If there were just one or two times, we’d list those. If it were more often, we had “every hour” or “every 15 minutes”.

    This wasn’t perfect, but it made most schedules clear.

    Job Names and Descriptions

    We tried to make job names clear with a starting noun (Backup, Maintenance, Sales), which was a little overloaded. It was a DBA thing for most work that DBAs might run and a department for those business level things.

    Job Steps

    I tried desperately to get away from code in the job step and use stored procedures instead. This helps us tune and watch things that run, and it keeps code in code places.

    For DBA stuff, we had a DBA database on each instance for our procs. We’d put our code in there (Ola’s procs, our own custom maintenance things, checks, ETL, etc.). This way we could more easily run server level stuff.

    For business level jobs or things related to a db, we want a proc in there. Then call that. This also let us often have a logging table alongside the proc where we could track progress.

    Alerts/Operators

    Luckily we had a monitoring solution that notified us when jobs failed. We didn’t use these systems. However, we did have an auditing report that queried DMVs and noted job failures and stored this data in a table (rolling 30 days) and used it to produce a daily report we archived in a folder.

    This was for our ISO compliance and auditors loved it. We would store a daily report and then add a daily note of any actions we took. That way we knew what we did and had a record.

    Summary

    For most of the other options (categories, etc.) we ignored them. The goal was to keep things very simple and streamlined. We had a standard job we deployed to most servers as part of a build process.

    We also drove a lot of activity in code off queries as much as possible and only used a table to log exceptions. We might have a table that stored the “FinanceDW” db name as an exception. The backup process would get a list of all dbs, and then delete those in the exception table. Then run as normal.

    K.I.S.S. worked very well for us.

  • T-SQL Tuesday #185–Being a Video Star

    tsqltuesdayThis month we have an interesting invite. Erik Darling is the host, and since he does a lot of video blogs, he’s asking for a video submission for T-SQL Tuesday. The topic and content is up to each of us, but the format is video.

    That’s funny as I’m writing this blog, but the content below is in the video. What’s in there? You have to watch.

    I’m also looking for hosts for the future and have a few slots in 2025 still available for H2. I’ll also note that any month could be a video post for the topic if you want, though I suspect a few hosts might not love having to watch videos. Some, however, will love it.

    My Submission – Testing the Flat File Wizard

    Here’s my submission:

  • T-SQL Tuesday #184–Mentoring

    This month’s T-SQL Tuesday blog party is hosted by Deborah Melkin, and it’s a good one that asks us where we are making the world better. The topic is mentorship and sponsorship, which is great. We all ought to pay it forward or pay it back, depending on how lucky we are. I believe strongly in this and am glad to see the topic posted.

    I still manage the T-SQL Tuesday list, and I’m always looking for hosts. I have a few scheduled for 2025, but I can use more. If you’re interested in hosting, hit me up at one of these places:

    The Mentoring Experiment

    Andy Warren and I ran The Mentoring Experiment a few times in the past before life got too hard and we decided to pause. I wrote about mentoring as well, and didn’t publish a lot on the experiment as most of the conversations were private.

    I’m somewhat sorry we didn’t continue this, but it proved to be a little overwhelming at that time in our lives.

    My Mentoring Experiences

    I’ve had a few mentors in my life, and while some I’m not comfortable sharing, there are a few that stand out.

    In high school, I had a mentor who was a Navy Pilot. He was a client of my Mom’s and she asked him to give me rides to and from karate, where he was also a student. On our rides, he shared some advice, some thoughts on life, while taking an interest in me. A few things stand out, one of which I still think about today: before I send an email, how would I feel if this were made public? This keeps me from writing too emotionally.

    For the last 20+ years, Andy Warren and I have talked most weeks of the year. We miss a few when we’re on vacation, or I’m traveling, but he has been a great mentor to me, helping me think through life’s challenges. I hope I’ve done the same for him.

    Giving Back

    I think part of what I should do is try and make the world better. Part of that is my trying to get a variety of people to write at SQL Server Central or host here. I’ve reached out through my network to find women or minorities that would participate in our data community, and give them a voice. I’ve been less successful than I would have liked, but I have had a little success.

    I’ve also convinced a few people to speak. I usually look for people at events that are engaged, ask interesting questions, or just have good conversations with me. I’ve found it takes months, but if I encourage them, I’ve gotten some of them to write or speak for free to share their knowledge and grow their own skills. There are a couple successes who many of you likely know their name and a few more that tried it and gave up.

    I think the more you encourage people to engage in life, the more they (and you) get out of it.

    I also try to do this as a coach, getting them to grow and learn more than just the sport. I think it’s worked out well as many kids keep in touch over the years.

    I think many of you can do the same thing. Help others grow and find their own success, with encouragement, support, and a friendly ear.