Query to review job status

Queries all enabled agent jobs and returns either jobs not in success status or jobs where the run time is greater than the run time of the last X runs + the standard deviation of the last X runs. The default is 10 but is set in the opening @TargetDate statement.This will reduce the number of jobs that need interrogating.


DECLARE @TargetDate DATE = DATEADD(d, -10, GETDATE());
--PRINT @TargetDate;
--PRINT FORMAT(MONTH(@TargetDate), 'd2');
DECLARE @RunTo INT = CAST(CAST(YEAR(@TargetDate) AS NCHAR(4)) + FORMAT(MONTH(@TargetDate), 'd2') + FORMAT(DAY(@TargetDate), 'd2') AS INT);

DECLARE @tJobHistory TABLE 
(job_id uniqueidentifier
, avgRunDurationLast10 decimal(10,2)
, stdRunDurationLast10 decimal(10,2)
);

WITH cteLast10DaysJobs AS
(
SELECT
job_id
, run_duration
FROM msdb..sysjobhistory
WHERE step_id = 0
AND run_status = 1
AND run_date >= @RunTo
)
INSERT INTO @tJobHistory
SELECT
job_id
,AVG(CAST (run_duration as float))as average_run_duration
,COALESCE(STDEV(CAST (run_duration as float)), 0) as stddev_run_duration
FROM cteLast10DaysJobs
--WHERE  job_id = '0AF6CA6E-E573-4FB8-B065-EE9994F3898D'
GROUP BY job_id

SELECT
sj.name AS Job_Name
,sjh.[message] AS Job_Message
, CASE sjh.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
        WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
END AS Job_Status
,sjh.run_date AS Last_Run_Date
,sjh.run_time AS Job_Last_Run_Time
,sjh.run_duration AS Total_Run_Time
,jh.avgRunDurationLast10 AS Avg_Run_Time_Last_10_Runs
, jh.stdRunDurationLast10 AS  Stddev_Run_Time_Last_10_Runs
, sjh.run_duration - jh.avgRunDurationLast10 - jh.stdRunDurationLast10 AS Run_Time_Greater_Then_Dev
FROM msdb..sysjobs sj
INNER join @tJobHistory jh
ON sj.job_id = jh.job_id
INNER join msdb..sysjobhistory sjh
ON sj.job_id = sjh.job_id
WHERE sj.enabled = 1
AND sjh.step_id = 0
AND (sjh.run_duration - round(jh.avgRunDurationLast10,2) > jh.stdRunDurationLast10  OR run_status != 1)
AND instance_id in (SELECT MAX(instance_id) FROM msdb..sysjobhistory GROUP BY by job_id)
ORDER BY
run_date desc
, sjh.run_duration - jh.avgRunDurationLast10 - jh.stdRunDurationLast10 DESC;

SQL Jobs change job step proxy

The purpose of this script is to set a proxy on each job step to allow the owner of the job to not have access to the target database. This applies when a separate server is used for ETL and data storage. This also has a strong effect on the performance of the database server as significantly less memory has to be allocated to the Integration Services service.

This assume you can segregate the jobs by job category to make sure only the targeted step are changed. This could also be done easily by job owner.


USE MSDB
GO

SELECT * FROM sysproxies;
--Use this to restrict the jobs to only the categories you want to change
DECLARE @categoryMatch nvarchar(30) = 'test_%';
--Set the proxy value to the values desired in the above query
DECLARE @proxy_value nvarchar(10) = 2;
DECLARE @CurJobId nvarchar(50);
DECLARE @curStepId nvarchar(50);

SELECT sj.job_id, sjs.step_id, proxy_id
FROM sysjobs sj
INNER JOIN sysjobsteps sjs
ON sj.job_id = sjs.job_id
WHERE category_id IN ( select category_id FROM syscategories WHERE  name like @categoryMatch)
AND (sjs.proxy_id != @proxy_value OR proxy_id IS NULL);

DECLARE db_cursor CURSOR FOR
SELECT sj.job_id, sjs.step_id
FROM sysjobs sj
INNER JOIN  sysjobsteps sjs
ON sj.job_id = sjs.job_id
WHERE  category_id IN (SELECT category_id FROM syscategories WHERE  name like @categoryMatch)
AND (sjs.proxy_id != @proxy_value OR proxy_id IS NULL);


OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @CurJobId, @curStepId

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Job_ID:  ' + @curJobId + '     Job Step:  ' + @curStepId;
EXEC dbo.sp_update_jobstep
@job_id = @curJobId
,@step_id = @curStepId
,@proxy_id = @proxy_value;

    FETCH NEXT FROM db_cursor INTO @CurJobId, @curStepId
END

CLOSE db_cursor
DEALLOCATE db_cursor

Fail a jobs is any pre-requisite jobs have failed

USE [msdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*
Used to detemine if any jobs in a category have failed
All jobs must be assigned to the same category

Parameters:
@JobCategoryName - The category name set up in hte local instance
@HoursToCheck - the number of hours back in history you want the call to look. DEFAULT = 24
@IsSuccessful - Whether or not any jobs meet the criteria
To fine all failed jobs put 0

RETURNS
0 = No Jobs in the tested status
1 = Jobs exist in the tested status

Example of use to fail a job when any jobs in the category have a failure
IF (SELECT [dbo].[CheckJobCategoryStatus] ('SAP_to_staging', 24, 0)) = 1
  BEGIN
    RAISERROR('SAP_to_staging pre-requisite has failed. Re-run all failed prerequisites', 16, 1)
  END
*/

CREATE FUNCTION [dbo].[CheckJobCategoryStatus]
(@JobCategoryName nvarchar(50)
, @HoursToCheck int = 24
, @IsSuccessful bit = 0
)
RETURNS bit
AS 
BEGIN
--DECLARE @JobCatagory nvarchar(50) = 'SAP_to_staging';
--DECLARE @HoursToCheck int = 24;
--DECLARE @IsSuccessful bit = 1;

DECLARE @IsCategoryStatusTrue bit = 0;

IF EXISTS(SELECT
SJ.NAME AS [Job Name]
,RUN_STATUS AS [Run Status]
,MAX(DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME)) AS [Last Time Job Ran On]
FROM dbo.SYSJOBS SJ
LEFT OUTER JOIN dbo.SYSJOBHISTORY JH
ON SJ.job_id = JH.job_id
WHERE JH.step_id = 0
AND jh.run_status = @IsSuccessful
and category_id IN (select category_id
from dbo.syscategories
where name = @JobCategoryName)
and DATEDIFF(HH , DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME), getdate()) <= @HoursToCheck
GROUP BY SJ.name, JH.run_status)
SET  @IsCategoryStatusTrue = 1;
ELSE
SET  @IsCategoryStatusTrue = 0;
RETURN @IsCategoryStatusTrue;
END


GO

Update to handle snapshots and not try to back them up in any multi-database queries

SELECT DISTINCT
s_mf.database_id
, *
FROM sys.master_files s_mf
INNER JOIN sys.databases s_db
ON s_mf.database_id = s_db.database_id
WHERE s_mf.state = 0 -- Online
AND s_db.source_database_id is null --Eliminate All Snapshots
AND has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
AND NOT db_name(s_mf.database_id) in ('tempdb', 'master', 'msdb', 'model') --eliminate temp db

Searching for table inforation in SQL jobs

I'm putting this here to make it easier to look at job and job step information quickly.


USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[JobInformation_SqlSSISJobs_SelectBySSISFileName]
Script Date: 06/24/2009 13:32:21 ******

This script is used to return every job step where a string occurs

*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE procedure [dbo].[JobInformation_SqlSSISJobs_SelectBySSISFileName]
@fileName as varchar(125)
AS

/*
declare @fileName as varchar(125)
set @fileName = 'Order Invoice Import Data.dstConfig'
*/

select
[name] as JobName
, step.step_id as JobStepNbr
, step.step_name as JobStepName
, subsystem as JobStepType
, command as JobStepCommand
, database_name AS JobStepDbName
, last_run_date AS JobStepLastRunDate
, last_run_time AS JobStepLastRunTime
, next_run_date AS JobStepNextRunDate
, next_run_time AS JobStepNextRunTime
, run_status AS JobStepLastRunStatus
--, hist.*
FROM msdb.dbo.sysjobs job
INNER JOIN msdb.dbo.sysjobsteps step
ON job.job_id = step.job_id
INNER JOIN msdb.dbo.sysjobschedules sch
ON job.job_id = sch.job_id
LEFT OUTER JOIN dbo.sysjobhistory hist
ON job.job_id = hist.job_id
AND step.step_id = hist.step_id
AND step.last_run_date = hist.run_date
AND step.last_run_time = hist.run_time
WHERE command like '%' + @fileName + '%';



----------------
Now playing: NPR - 0906171: NPR: 06-20-2009 Wait Wait... Don't Tell Me!
via FoxyTunes