Registration for the 2021 PASS Data Community Summit opened today. This year’s event is virtual on November 8-12, and you can register for free.
That’s right, free. No cost, just the time, which is something you might point out to your boss to get some time that week to attend.
Join me and see my sessions, or any of the others that have been selected. There are plenty of great ones to choose from. Register today and I’ll see you online in November.
I started to add a daily coping tip to the SQLServerCentral 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.
Today’s tip is to if someone annoys you, be kind. Imagine how they are feeling.
I was in a meeting recently, and someone was a little annoying to me. In this case, it wasn’t what they did, but rather what they didn’t do, which was be prepared or communicate well. Time is valuable to me, I hate Zoom meetings more than I expected, and I sometimes find myself losing some patience when there aren’t productive presentations or discussions.
I did find patience, and I considered just how the person might have had a bad week. They might be struggling. In fact, in this case, I knew the person had some issues outside of work, so I accepted the less than efficient meeting. Asked questions, was pleasant, and voiced no complaints.
We go through, things worked out, and it wasn’t a big impact to my day. I didn’t let it, and more importantly, I didn’t ruin anyone else’s day.
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.
Many of us reading this are data professionals, and we likely know quite a bit about SQL in general. We may use T-SQL, specifically, with SQL Server, but many of the skills we have would be portable to other dialects, such as those use in Oracle, PostgreSQL, etc. We’d certainly need to brush up on best practices and which language constructs are better suited for a specific platform, but most of the knowledge transfers.
I ran across an interesting post on the value of SQL in a career, mostly for someone that might be moving into the data science or machine learning/AI type role. The post notes that lots of these job descriptions mention SQL, but focus quite a bit on R, Python, modeling, etc. In this case, SQL matters, and I’d agree with the author that it matters a lot. Most of the work in those fields is data prep, and SQL makes this much easier at scale than other languages.
What about those of us that have been working with SQL Server (or some other platform) for awhile? Is learning more about SQL a good investment in our career? In most cases, does our boss even know if we have mediocre or amazing T-SQL skills? Do they care?
They may not, but I think they should care, and more importantly, you may care. When you know more about the language and how to structure queries to solve problems, you’ll work quicker. You will write code that performs better, resulting in a lower workload on the instances. Your code will last longer, have fewer bugs, and co-workers will trust your work.
The more you practice with code, the more you solve new problems and learn what works well and what doesn’t, the better you will be at your job. The time you put into learning to write better queries will pay back with less stress and more time for other tasks. While your boss might not notice your code is better, they certainly will see you as more capable, relaxed, and trustworthy. All good impressions to make at review time.
Steve Jones
Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.
Voice of the DBA 