MSSQLTips Monday Mission - Failed SQL Server Agent Jobs Script

[MSSQLTips.com logo]( [View email in your browser]( TODAY'S NEWS FROM MSSQLTips Learn something new every day! [mssqltips image]( [Google Gemini - Generative AI Tool for SQL Professionals]( This article looks at using Google's artificial intelligence tool Gemini to build and explain T-SQL code.... [Read Now]( [mssqltips image]( [Navigate SQL Server End of Support (EOS) Challenges]( As a SQL Server professional, you are familiar with Microsoft regularly sunsetting support. The most imminent of these events is the end of support (EOS) for SQL Server 2014 on July 9, 2024. After the EOS date, Microsoft will no longer provide patche... [Read Now]( [mssqltips image]( [AZ-500 Microsoft Azure Security Exam Study Guide]( Check out this article for lots of study links and resources to help you pass the Microsoft exam AZ-500 Microsoft Azure Security Technologies.... [Read Now]( TODAY'S SCRIPT ON MSSQLTips MSSQLTips Monday Mission - Failed SQL Server Agent Jobs On MSSQLTips, our goal is to help you best manage your Microsoft Data Platform - on-prem, in a data center or in the cloud. SQL Server Agent is responsible for both business and DBA processes. Missing a failed job can have numerous downstream ramifications. Today's mission, should you choose to accept it, is to find failed SQL Server Agent Jobs. -- Purpose: This query will return information about recent SQL Server Agent jobs that did not succeed. USE msdb GO DECLARE @DaysBack INT = 3; -- Review data for the last 3 days. SELECT sj.[name] JobName , sjh.step_name , message , dbo.agent_datetime(sjh.run_date, sjh.run_time) JobRunTime , CASE WHEN sjh.run_duration > 235959 THEN CAST((CAST(LEFT(CAST(sjh.run_duration AS VARCHAR), LEN(CAST(sjh.run_duration AS VARCHAR)) - 4) AS INT) / 24) AS VARCHAR) + '.' + RIGHT('00' + CAST(CAST(LEFT(CAST(sjh.run_duration AS VARCHAR), LEN(CAST(sjh.run_duration AS VARCHAR)) - 4) AS INT) % 24 AS VARCHAR), 2) + ':' + STUFF(CAST(RIGHT(CAST(sjh.run_duration AS VARCHAR), 4) AS VARCHAR(6)), 3, 0, ':') ELSE STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(sjh.run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') END AS Duration FROM dbo.sysjobs sj INNER JOIN dbo.sysjobhistory sjh ON sj.job_id = sjh.job_id WHERE sjh.run_status 1 --1 = success AND dbo.agent_datetime(sjh.run_date, sjh.run_time) > DATEADD(dd, ABS(@DaysBack) * -1, SYSDATETIME()) ORDER BY sj.[name] , sjh.step_id , dbo.agent_datetime(sjh.run_date, sjh.run_time); GO Learn more and share your feedback: [Find Failed SQL Server Agent Jobs]( RECOMMENDED READING ON MSSQLTips [mssqltips image]( [Easy and Cost Effective way to Encrypt Every SQL Server Database]( Learn why Becton Dickinson has selected DBDefence to protect their SQL Server data across numerous medical devices in a cost effective manner.... [Read Now]( [mssqltips image]( [Learning SQL Server]( I am new to SQL Server and I have heard the term used in a number of different capacities and in different contexts. So what exactly is it? What are the common components? How does it work? How do people use it? I am brand new to the technology, wher... [Read Now]( [mssqltips image]( [SQL Server Best Practices for Data Quality]( Data Quality | SSIS Integration - Accurate data is imperative for an organization to conduct cost effective decision making. Like everything else, change is constant for your data. There is a need to cleanse and validate data when received and on a ... [Read Now]( [Manage MSSQLTips Subscriptions | Unsubscribe](. Edgewood Solutions LLC PO Box 682 | Wilton, NH 03086 [Privacy Statement]( Sent to: {EMAIL} [tag]