Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #188: Growing the Community

    This month we have a great T-SQL Tuesday, hosted by John Sterrett. He has been a great help in multiple communities over the years, and he has quite an inspirational invitation when you read his story. I am honored to call John a friend and enjoy the times we get to sit and chat.

    If you aren’t a part of T-SQL Tuesday, start a blog at WordPress or Medium, or even on LinkedIn. Write a post, leave a link on John’s invitatin, and become a bigger part of our community. If you want to host a T-SQL Tuesday let me know.

    Working in the Community

    Quite a bit of my career has been intertwined with community. When we started SQL Server Central, we didn’t expect the community to grow like it did. We thought we’d help teach people, but the forums have become an amazing community. If you ever need help, there are plenty of people who can help, arguably better than a GenAI.

    From there, my partners, Andy Warren and Brian Knight, and I founded SQL Saturday. I continue to run that today, helping local volunteers run events and fund them.

    However, the things I’ve been most proud of is convincing some of the people in the Denver area to deliver their first presentation, helping to inspire and educate their local community. I’ve made it a point over the years to look for, and listen for, smart people and then encourage, support, and inspire them to try speaking. Getting new people to share information is always exciting and thrilling for me, and it adds to the community aspect of my life.

    I’ve been less successful in getting others to blog, but still, each time I see someone start blogging, I’m grateful they made the time and overcame their fears to share something with the world.

    A rising tide lifts all boats, and a community is an incredible tide.

  • 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: