Voice of the DBA

Tag: administration

Finding Failed Job Steps
Kendra’s query was a good starting point, and I used most of it in the first CTE shown below. This query basically looks at msdb.dbo.sysjobhistory and msdb.dbo.sysjobactivity, joining them on the job_id, which is the PK. However, we are only looking at steps, which is anything with a step_id > 0. The step_id = 0 is for the overall job.Recently a customer was asking for a way to alert on job steps that failed, but the job succeeded. They really wanted a custom metric for SQL Monitor, which I submitted, but this post looks at the query in general, trying to determine if a job step failed.

Note: Let me start by noting that this is based on work by Kendra from her post, SQL Agent Jobs: Checking for failed steps at the end of a job.

Based on Kendra’s query, I looked through what is happening in msdb.dbo.sysjobhistory and msdb.dbo.sysjobactivity. In Kendra’s query, she is looking for a specific job, but I wanted all jobs. This lead me to build a CTE that queries for the data.

As you can see in the code below, I use most of Kendra’s query to join these two tables together. First, we look for steps, so step_id != 0, and we look for failures. A status of 0 is a failure here, where 1 is success.

Update: Changed the query after the comment and another bug I noticed.
```
WITH cteActivity (job_id, start_execution_date)
AS ( -- get the latest job execution for all jobs
    SELECT job_id,
           MAX(start_execution_date) AS start_execution_date
    FROM msdb.dbo.sysjobactivity
    GROUP BY job_id)
, cteJobStep (Job_ID, Step_Name, run_date, run_time)
    AS (SELECT jh.job_id,
               jh.step_name AS JobStepName,
               jh.run_date,
               jh.run_time
        FROM msdb.dbo.sysjobhistory jh
            INNER JOIN cteActivity ja
                ON jh.job_id = ja.job_id
        WHERE jh.run_status = 0 --step failed
              AND jh.step_id != 0
              --         and jh.job_id = CONVERT(uniqueidentifier, '8C673935-F8C1-4E7D-94D3-1F3CAE50D7DC')
              AND --this block ensures that we just pull information from the most recent job run
            (
            -- The start time of the step, converted to datetime
            CONVERT(DATETIME, RTRIM(jh.run_date))
            + (jh.run_time * 9 + jh.run_time % 10000 * 6 + jh.run_time % 100
               * 10
              ) / 216e4 >= ja.start_execution_date -- the time the job last started
            )
```
Next, I kept most of Kendra’s query, but I commented out the line that limits this to a specific ID. I just want all step failures. I did keep the part that only checks the latest execution of the job.

The outer query just counts these up and returns a number. This lets me know how many job steps have failed during their latest execution.

This is a good first step, but this is something I could add in SQL Monitor as a custom metric or in any tool for alerting. When I have steps failing, I might want to know.
August 18, 2021
More Interactive Notebooks from MS Docs
I saw a note from Microsoft recently that they are trying to improve some of the support experiences for customers that are going through their docs. The idea is to make it easier for a customer to get a rich code experience for troubleshooting or learning.

This is a preview feature, but here’s a quick look.

Full Transaction Logs

For some people, the transaction log gets full. Not for me, but it has happened in the past, and I’ve learned how to be proactive here. However, this topic might be the number one topic at SQLServerCentral over the years.

Microsoft has a docs article on troubleshooting a full transaction log. In the article, at the top, is a new button. You can see here, this will open a notebook in ADS.

When I click this, I get a security warning and do I want to open this with an app.

Then I get the app chooser:

Then ADS opens.

Well, kind of. I have to approve something else.

And again.

I appreciate secure computing and good practices, but this type of clicking again and again is going to get people to do the “run everything as sa” security mindset and start to always allow things to open.

In any case, I do get a notebook. The text is the text of the KB article, which is comprehensive.

The code is included, but I can run this case. Not cut/paste, not mess with, just connect to an instance and run it.

There are instructions for fixing things.

And there are options for how to solve different issues, some of which also have code.

To me, this is a great way to help customers quickly solve issues. I look forward to seeing how this goes, and how people use it. For now, I know there are two articles in preview, if you want to give this a try.
- MSSQLSERVER_35250
- Troubleshoot a Full Transaction Log (SQL Server Error 9002)
August 11, 2021
Deploying SQL Server Automatically

I’ve had to install SQL Server many times over the years. Often it has felt that most of these installs were one-offs, a dev server, a new QA instance, a production server for a brand new application. A few times I’ve had to recover from disaster, including restoring master, but often, I just installed SQL Server manually because that was quick and guaranteed. I knew what needed to be done for most of the instances.

However, I couldn’t be sure. In the past there were relatively few settings that were ever changed outside of the master database, but today there are more, and perhaps more importantly, the tolerance for making mistakes with any security missteps, is low. I’ve spent a bit of time learning to do unattended installs, and I’ve tried systems like FineBuild for installing SQL Server. In any size organization that might want certain standards set up, using one of these systems is important. Perhaps one of the better reasons to do this is ensure that your dev and test systems are configured the same as production to prevent any silly mis-configuration problems.

This week I saw a post on using Ansible to install SQL Server on Linux, with all the various configuration. While I do think that it’s easier to install SQL Server on Linux programmatically, there are a number of items to set and configure. Following along the install, it’s interesting how much more this feels like something a developer would do, which is how many modern Operations groups approach installs and upgrades. Everything needs to be done without a human directly involved, often because of scale. There are so many systems we manage, often a mix of VMs, cloud, and local systems, which mean that a consistent, programmatic way of installing instances is needed.

That’s likely one of the most important skills for a modern system administrator. We need to learn to use tools to get our work done. Whether these are free, open-source, or purchased, using a tool to work at scale is more important today than ever before. Even if you are part of a development group, learning to manage systems with code can help ensure you can scale and grow quickly, and more importantly, hand off this job easily to someone else when you reach that point.

I think there are plenty of homemade scripts and tools that can help here, but almost every time I’ve seen one of these, it’s not very portable to other staff, especially if the author isn’t available. Too often these tools take shortcuts or are specifically tailored to the current environment and not the future one. These days, for many functions we tackle there are extremely well built tools available at no, low, or modest costs. I’d encourage anyone that is handling these tasks to learn about the tools available and pick one. I don’t know that I think any of these are necessarily easier to use, but they are all well documented and capable of handling most of your install, update, and configuration chores.

Steve Jones

July 31, 2021
Essential Operations

It would seem obvious, but IT Operations staff are often essential to ensuring that systems and business can keep running. From websites to email to VPNs, many of the companies in which I’ve worked don’t have systems that will run without someone keeping an eye on them. Sometimes many eyes on a daily basis if we want to ensure that clients can log into applications and use them.

The pandemic of the last year has shown many organizations that workers can get their jobs done from any location. Very few of us actually touch the physical hardware that we might manage, but we do need access to those systems across networks, in a secure way. That might not have been as big a challenge as getting the rest of a company online, but it was a lot of work.

I don’t know how many people prioritize or consider IT operations an essential worker. This article makes a case that anyone working in IT Ops ought to be classified in the same way that health-case workers, teachers, and more. These workers were needed to keep the world moving forward, so why not recognize the importance of IT Ops along with these other types of workers.

I do agree that Operations staff need to be treated as important pieces in an organization. Even if you use a lot of DevOps automation, GitOps, Infrastructure-as-Code, and cloud resources, you need staff to track, configure, manage, monitor, and adjust the way things work. There likely isn’t any organization that has an infrastructure they can run without any humans involved on a weekly basis. Likely something would break in that time and you need some staff.

While you might consider the operations staff to be more replaceable and less valuable than developers or others, keep in mind that bringing someone up to speed on existing systems and ensuring they can run without service interruption isn’t as simple as just grabbing a new worker from some temp agency.

If you use computer services from your company, remember that someone is ensuring they continue to run smoothly for you, even if you don’t realize what they are doing. Remember there is someone that is behind the scenes, often during nights and weekends, patching, upgrading, and monitoring equipment. Thank them, send some appreciation, and acknowledge their effort the next time you have the opportunity. I’ve done that job, and I know it can be thankless. I’m going to take a moment and drop a note to my own staff, who really make my life much easier.

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

April 22, 2021