How to monitor your SQL Server instances..(4/10)?

Hello every body in the previous post we figured more detail about How to monitor and mange your SQL Server jobs ..? but now all thing What we Covered in the Previous Post ..? we Covered 10 Point

Check SQL Server jobs First Vision..?
Check jobs Status ..? Is it enabled or Disabled ..?
When was SQL agent job was created or when was last time it was modified..?
Identify newly created SQL Server Agent Jobs
Check enabled jobs Without notification setup ..?
retrieving enabled operators in SQL Server Agent
Update SQL Server Agent Jobs without any notification by New notification
SQL Server Agent Job Setup and Configuration Information..
Check jobs With Enabled Schedule ..
Check SQL Server Agent Job Schedule Information..

and i will going today to complete this Subject by Covering another Impressive point :

Configuring SQL Agent Jobs to Write to Windows Event Log.
Generate SQL Agent Job Schedule Report
SQL Server Agent Job Setup and Configuration Information
SQL Server Agent Job Steps Execution Information
Jobs Report by OnSuccessAction and on_fail_action
Check or Change job owner
List by jobs are ruining now on your DB Server.
Jobs With Execution Long Time .
Select Failed Job
How to search on your Jobs TEXT.

let’ s drill down to figure this 10 new point one by one .

Configuring SQL Agent Jobs to Write to Windows Event Log.

at the first we need to know the jobs not configured to write in the event log

SELECT [name]

 FROM msdb..sysjobs

 WHERE [notify_level_eventlog] = 0

Let’s Configured this job to write in the Event log

UPDATE msdb..sysjobs

 SET [notify_level_eventlog] = 2

 --WHERE [name] like '%Your Joba Name%'

Generate SQL Agent Job Schedule Report

by this report we can figured the

Name : Job Name

active_start_date : Start time for the job Execute.

ScheduleDscr : Description for the job Schedule

enabled : IF = 1 (job Enabled ) IF = 0 (Job Disable)

to generate this report we have 2 Steps First one Create Function , Second one the DMV using CTE (Conman Table Expression )

CREATE FUNCTION [dbo].[udf_schedule_description] (@freq_type INT ,

 @freq_interval INT ,

 @freq_subday_type INT ,

 @freq_subday_interval INT ,

 @freq_relative_interval INT ,

 @freq_recurrence_factor INT ,

 @active_start_date INT ,

 @active_end_date INT,

 @active_start_time INT ,

 @active_end_time INT )

RETURNS NVARCHAR(255) AS

BEGIN

DECLARE @schedule_description NVARCHAR(255)

DECLARE @loop INT

DECLARE @idle_cpu_percent INT

DECLARE @idle_cpu_duration INT

IF (@freq_type = 0x1) -- OneTime

BEGIN

SELECT @schedule_description = N'Once on ' + CONVERT(NVARCHAR, @active_start_date) + N' at ' + CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2))

RETURN @schedule_description

END

IF (@freq_type = 0x4) -- Daily

BEGIN

SELECT @schedule_description = N'Every day '

END

IF (@freq_type = 0x8) -- Weekly

BEGIN

SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' week(s) on '

SELECT @loop = 1

WHILE (@loop <= 7)

BEGIN

IF (@freq_interval & POWER(2, @loop - 1) = POWER(2, @loop - 1))

SELECT @schedule_description = @schedule_description + DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @loop)) + N', '

SELECT @loop = @loop + 1

END

IF (RIGHT(@schedule_description, 2) = N', ')

SELECT @schedule_description = SUBSTRING(@schedule_description, 1, (DATALENGTH(@schedule_description) / 2) - 2) + N' '

END

IF (@freq_type = 0x10) -- Monthly

BEGIN

SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on day ' + CONVERT(NVARCHAR, @freq_interval) + N' of that month '

END

IF (@freq_type = 0x20) -- Monthly Relative

BEGIN

SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on the '

SELECT @schedule_description = @schedule_description +

CASE @freq_relative_interval

WHEN 0x01 THEN N'first '

WHEN 0x02 THEN N'second '

WHEN 0x04 THEN N'third '

WHEN 0x08 THEN N'fourth '

WHEN 0x10 THEN N'last '

END +

CASE

WHEN (@freq_interval > 00)

AND (@freq_interval < 08) THEN DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @freq_interval))

WHEN (@freq_interval = 08) THEN N'day'

WHEN (@freq_interval = 09) THEN N'week day'

WHEN (@freq_interval = 10) THEN N'weekend day'

END + N' of that month '

END

IF (@freq_type = 0x40) -- AutoStart

BEGIN

SELECT @schedule_description = FORMATMESSAGE(14579)

RETURN @schedule_description

END

IF (@freq_type = 0x80) -- OnIdle

BEGIN

EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',

N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',

N'IdleCPUPercent',

@idle_cpu_percent OUTPUT,

N'no_output'

EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',

N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',

N'IdleCPUDuration',

@idle_cpu_duration OUTPUT,

N'no_output'

SELECT @schedule_description = FORMATMESSAGE(14578, ISNULL(@idle_cpu_percent, 10), ISNULL(@idle_cpu_duration, 600))

RETURN @schedule_description

END

-- Subday stuff

SELECT @schedule_description = @schedule_description +

CASE @freq_subday_type

WHEN 0x1 THEN N'at ' + CONVERT(NVARCHAR, cast((@active_start_time / 10000) asvarchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 asvarchar(10)),2))

WHEN 0x2 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' second(s)'

WHEN 0x4 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' minute(s)'

WHEN 0x8 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' hour(s)'

END

IF (@freq_subday_type IN (0x2, 0x4, 0x8))

SELECT @schedule_description = @schedule_description + N' between ' +

CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' +right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2) ) + N' and ' + CONVERT(NVARCHAR, cast((@active_end_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_end_time % 10000) / 100 as varchar(10)),2) )

RETURN @schedule_description

END

With CTE AS (

SELECT dbo.sysjobs.name, CAST(dbo.sysschedules.active_start_time / 10000 ASVARCHAR(10))

+ ':' + RIGHT('00' + CAST(dbo.sysschedules.active_start_time % 10000 / 100 ASVARCHAR(10)), 2) AS active_start_time,

dbo.udf_schedule_description(dbo.sysschedules.freq_type,

dbo.sysschedules.freq_interval,

dbo.sysschedules.freq_subday_type,

dbo.sysschedules.freq_subday_interval,

dbo.sysschedules.freq_relative_interval,

dbo.sysschedules.freq_recurrence_factor,

dbo.sysschedules.active_start_date,

dbo.sysschedules.active_end_date,

dbo.sysschedules.active_start_time,

dbo.sysschedules.active_end_time) AS ScheduleDscr, dbo.sysjobs.enabled

FROM dbo.sysjobs INNER JOIN

dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id INNERJOIN

dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id

) Select * from CTE

where ScheduleDscr Like '%DAY%'-----Option

Information for SQL Server Job Configuration

by this Script we can now all things bout our job Configuration

<em>

SELECT

 [sJOB].[job_id] AS [JobID]

 , [sJOB].[name] AS [JobName]

 , [sDBP].[name] AS [JobOwner]

 , [sCAT].[name] AS [JobCategory]

 , [sJOB].[description] AS [JobDescription]

 , CASE [sJOB].[enabled]

 WHEN 1 THEN 'Yes'

 WHEN 0 THEN 'No'

 END AS [IsEnabled]

 , [sJOB].[date_created] AS [JobCreatedOn]

 , [sJOB].[date_modified] AS [JobLastModifiedOn]

 , [sSVR].[name] AS [OriginatingServerName]

 , [sJSTP].[step_id] AS [JobStartStepNo]

 , [sJSTP].[step_name] AS [JobStartStepName]

 , CASE

 WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'

 ELSE 'Yes'

 END AS [IsScheduled]

 , [sSCH].[schedule_uid] AS [JobScheduleID]

 , [sSCH].[name] AS [JobScheduleName]

 , CASE [sJOB].[delete_level]

 WHEN 0 THEN 'Never'

 WHEN 1 THEN 'On Success'

 WHEN 2 THEN 'On Failure'

 WHEN 3 THEN 'On Completion'

 END AS [JobDeletionCriterion]

FROM

 [msdb].[dbo].[sysjobs] AS [sJOB]

 LEFT JOIN [msdb].[sys].[servers] AS [sSVR]

 ON [sJOB].[originating_server_id] = [sSVR].[server_id]

 LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]

 ON [sJOB].[category_id] = [sCAT].[category_id]

 LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]

 ON [sJOB].[job_id] = [sJSTP].[job_id]

 AND [sJOB].[start_step_id] = [sJSTP].[step_id]

 LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]

 ON [sJOB].[owner_sid] = [sDBP].[sid]

 LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]

 ON [sJOB].[job_id] = [sJOBSCH].[job_id]

 LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]

 ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]

ORDER BY [JobName]

SQL Server Agent Job Steps Execution Information

by this Script we can find all info related to the Steps and the Execution

SELECT

 [sJOB].[job_id] AS [JobID]

 , [sJOB].[name] AS [JobName]

 , [sJSTP].[step_uid] AS [StepID]

 , [sJSTP].[step_id] AS [StepNo]

 , [sJSTP].[step_name] AS [StepName]

 , CASE [sJSTP].[subsystem]

 WHEN 'ActiveScripting' THEN 'ActiveX Script'

 WHEN 'CmdExec' THEN 'Operating system (CmdExec)'

 WHEN 'PowerShell' THEN 'PowerShell'

 WHEN 'Distribution' THEN 'Replication Distributor'

 WHEN 'Merge' THEN 'Replication Merge'

 WHEN 'QueueReader' THEN 'Replication Queue Reader'

 WHEN 'Snapshot' THEN 'Replication Snapshot'

 WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'

 WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'

 WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'

 WHEN 'SSIS' THEN 'SQL Server Integration Services Package'

 WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'

 ELSE sJSTP.subsystem

 END AS [StepType]

 , [sPROX].[name] AS [RunAs]

 , [sJSTP].[database_name] AS [Database]

 , [sJSTP].[command] AS [ExecutableCommand]

 , CASE [sJSTP].[on_success_action]

 WHEN 1 THEN 'Quit the job reporting success'

 WHEN 2 THEN 'Quit the job reporting failure'

 WHEN 3 THEN 'Go to the next step'

 WHEN 4 THEN 'Go to Step: '

 + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3)))

 + ' '

 + [sOSSTP].[step_name]

 END AS [OnSuccessAction]

 , [sJSTP].[retry_attempts] AS [RetryAttempts]

 , [sJSTP].[retry_interval] AS [RetryInterval (Minutes)]

 , CASE [sJSTP].[on_fail_action]

 WHEN 1 THEN 'Quit the job reporting success'

 WHEN 2 THEN 'Quit the job reporting failure'

 WHEN 3 THEN 'Go to the next step'

 WHEN 4 THEN 'Go to Step: '

 + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))

 + ' '

 + [sOFSTP].[step_name]

 END AS [OnFailureAction]

FROM

 [msdb].[dbo].[sysjobsteps] AS [sJSTP]

 INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]

 ON [sJSTP].[job_id] = [sJOB].[job_id]

 LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]

 ON [sJSTP].[job_id] = [sOSSTP].[job_id]

 AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]

 LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]

 ON [sJSTP].[job_id] = [sOFSTP].[job_id]

 AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]

 LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]

 ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]

ORDER BY [JobName], [StepNo]

Jobs Report by OnSuccessAction and on_fail_action

SELECT

CASE [sJSTP].[on_success_action]

 WHEN 1 THEN 'Quit the job reporting success'

 WHEN 2 THEN 'Quit the job reporting failure'

 WHEN 3 THEN 'Go to the next step'

 WHEN 4 THEN 'Go to Step: '

 + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3)))

 + ' '

 + [sOSSTP].[step_name]

 END AS [OnSuccessAction]

 , CASE [sJSTP].[on_fail_action]

 WHEN 1 THEN 'Quit the job reporting success'

 WHEN 2 THEN 'Quit the job reporting failure'

 WHEN 3 THEN 'Go to the next step'

 WHEN 4 THEN 'Go to Step: '

 + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))

 + ' '

 + [sOFSTP].[step_name]

 END AS [OnFailureAction]

,[sJOB].[name] AS [JobName] ,

 [sJSTP].[step_id] AS [StepNo]

 , [sJSTP].[step_name] AS [StepName]

 , CASE [sJSTP].[subsystem]

 WHEN 'ActiveScripting' THEN 'ActiveX Script'

 WHEN 'CmdExec' THEN 'Operating system (CmdExec)'

 WHEN 'PowerShell' THEN 'PowerShell'

 WHEN 'Distribution' THEN 'Replication Distributor'

 WHEN 'Merge' THEN 'Replication Merge'

 WHEN 'QueueReader' THEN 'Replication Queue Reader'

 WHEN 'Snapshot' THEN 'Replication Snapshot'

 WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'

 WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'

 WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'

 WHEN 'SSIS' THEN 'SQL Server Integration Services Package'

 WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'

 ELSE sJSTP.subsystem

 END AS [StepType]

FROM

 [msdb].[dbo].[sysjobsteps] AS [sJSTP]

 INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]

 ON [sJSTP].[job_id] = [sJOB].[job_id]

 LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]

 ON [sJSTP].[job_id] = [sOSSTP].[job_id]

 AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]

 LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]

 ON [sJSTP].[job_id] = [sOFSTP].[job_id]

 AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]

 LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]

 ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]

 --Where [sJSTP].[on_success_action] = 2

 Where [sJSTP].[on_fail_action] = 1

ORDER BY [JobName], [StepNo]

[/sq]</pre>

<ul>

          <li><strong>Check or Change job owner</strong></li>

</ul>

<pre>

IF you have 50 job on your DB Server and you need to change the Job owner oneby one it will take more time so we will do DMV to manage this issue 

Check the Owner Job:

1

SELECT

 sv.name AS [Name],

 sv.job_id AS [JobID],

 l.name AS UserName

 FROM

 msdb.dbo.sysjobs_view AS sv

 INNER JOIN [master].[sys].[syslogins] l ON sv.owner_sid = l.sid

 ORDER BY

 sv.[Name] ASC

After we check the job owner now we need to change this job owner by anther one

DECLARE @Jobname Nvarchar

DECLARE @NewOwner varchar(200)

DECLARE @OldName varchar(200)

SET @NewOwner = 'New User Name'

SET @OldName = 'sa'

SELECT

sv.name AS [Name],

sv.job_id AS [JobID],

l.name AS [OwnerName]

INTO #SQLJobs

FROM

msdb.dbo.sysjobs_view AS sv

INNER JOIN [master].[sys].[syslogins] l ON sv.owner_sid = l.sid

WHERE l.name like @OldName

ORDER BY

sv.[Name] ASC

SELECT * FROM #SQLJobs

WHILE (SELECT COUNT(*) FROM #SQLJobs ) > 0 BEGIN

 SELECT TOP 1 @JobID = JobID FROM #SQLJobs

 EXEC msdb.dbo.sp_update_job @job_id= @JobID,

 @owner_login_name=@NewOwner

 DELETE FROM #SQLJobs WHERE JobID = @JobID

END

DROP TABLE #SQLJobs

List by jobs are ruining now on your DB Server.

IF EXISTS (SELECT *

FROM tempdb.dbo.sysobjects

WHERE id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]')

)

DROP TABLE [tempdb].[dbo].[Temp1]

GO

CREATE TABLE [tempdb].[dbo].[Temp1]

(

job_id uniqueidentifier NOT NULL,

last_run_date nvarchar (20) NOT NULL,

last_run_time nvarchar (20) NOT NULL,

next_run_date nvarchar (20) NOT NULL,

next_run_time nvarchar (20) NOT NULL,

next_run_schedule_id INT NOT NULL,

requested_to_run INT NOT NULL,

request_source INT NOT NULL,

request_source_id sysname

COLLATE database_default NULL,

running INT NOT NULL,

current_step INT NOT NULL,

current_retry_attempt INT NOT NULL,

job_state INT NOT NULL)

DECLARE @job_owner sysname

DECLARE @is_sysadmin INT

SET @is_sysadmin = isnull (is_srvrolemember ('sysadmin'), 0)

SET @job_owner = suser_sname ()

INSERT INTO [tempdb].[dbo].[Temp1]

--EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

UPDATE [tempdb].[dbo].[Temp1]

SET last_run_time = right ('000000' + last_run_time, 6),

next_run_time = right ('000000' + next_run_time, 6);

-----

SELECT j.name AS JobName,

j.enabled AS Enabled,

CASE x.running

WHEN 1

THEN

'Running'

ELSE

CASE h.run_status

WHEN 2 THEN 'Inactive'

WHEN 4 THEN 'Inactive'

ELSE 'Completed'

END

END

AS CurrentStatus,

coalesce (x.current_step, 0) AS CurrentStepNbr,

CASE

WHEN x.last_run_date > 0

THEN

convert (datetime,

substring (x.last_run_date, 1, 4)

+ '-'

+ substring (x.last_run_date, 5, 2)

+ '-'

+ substring (x.last_run_date, 7, 2)

+ ' '

+ substring (x.last_run_time, 1, 2)

+ ':'

+ substring (x.last_run_time, 3, 2)

+ ':'

+ substring (x.last_run_time, 5, 2)

+ '.000',

121

)

ELSE

NULL

END

AS LastRunTime,

CASE h.run_status

WHEN 0 THEN 'Fail'

WHEN 1 THEN 'Success'

WHEN 2 THEN 'Retry'

WHEN 3 THEN 'Cancel'

WHEN 4 THEN 'In progress'

END

AS LastRunOutcome,

CASE

WHEN h.run_duration > 0

THEN

(h.run_duration / 1000000) * (3600 * 24)

+ (h.run_duration / 10000 % 100) * 3600

+ (h.run_duration / 100 % 100) * 60

+ (h.run_duration % 100)

ELSE

NULL

END

AS LastRunDuration

FROM [tempdb].[dbo].[Temp1] x

LEFT JOIN

msdb.dbo.sysjobs j

ON x.job_id = j.job_id

LEFT OUTER JOIN

msdb.dbo.syscategories c

ON j.category_id = c.category_id

LEFT OUTER JOIN

msdb.dbo.sysjobhistory h

ON x.job_id = h.job_id

AND x.last_run_date = h.run_date

AND x.last_run_time = h.run_time

AND h.step_id = 0

where x.running = 1

CREATE TABLE #enum_job (

 Job_ID UNIQUEIDENTIFIER

 ,Last_Run_Date INT

 ,Last_Run_Time INT

 ,Next_Run_Date INT

 ,Next_Run_Time INT

 ,Next_Run_Schedule_ID INT

 ,Requested_To_Run INT

 ,Request_Source INT

 ,Request_Source_ID VARCHAR(100)

 ,Running INT

 ,Current_Step INT

 ,Current_Retry_Attempt INT

 ,STATE INT

 )

INSERT INTO #enum_job

EXEC master.dbo.xp_sqlagent_enum_jobs 1

 ,garbage

SELECT R.NAME

 ,R.last_run_date

 ,R.RunningForTime

 ,GETDATE() AS now

FROM #enum_job a

INNER JOIN (

 SELECT j.NAME

 ,J.JOB_ID

 ,ja.run_requested_date AS last_run_date

 ,(DATEDIFF(mi, ja.run_requested_date, GETDATE())

 ) AS RunningFor

 ,CASE LEN(CONVERT(VARCHAR(5), DATEDIFF(MI, JA.

 RUN_REQUESTED_DATE, GETDATE()) / 60))

 WHEN 1

 THEN '0' + CONVERT(VARCHAR(5), DATEDIFF(mi, ja.

 run_requested_date, GETDATE()) / 60

 )

 ELSE CONVERT(VARCHAR(5), DATEDIFF(mi, ja.

 run_requested_date, GETDATE()) / 60)

 END + ':' + CASE LEN(CONVERT(VARCHAR(5), (

 DATEDIFF(MI, JA.RUN_REQUESTED_DATE,

 GETDATE()) % 60

 )))

 WHEN 1

 THEN '0' + CONVERT(VARCHAR(5), (

 DATEDIFF(mi, ja.

 run_requested_date, GETDATE()

 ) % 60

 ))

 ELSE CONVERT(VARCHAR(5), (

 DATEDIFF(mi, ja.run_requested_date,

 GETDATE()) % 60

 ))

 END + ':' + CASE LEN(CONVERT(VARCHAR(5), (

 DATEDIFF(SS, JA.RUN_REQUESTED_DATE,

 GETDATE()) % 60

 )))

 WHEN 1

 THEN '0' + CONVERT(VARCHAR(5), (

 DATEDIFF(ss, ja.

 run_requested_date, GETDATE()

 ) % 60

 ))

 ELSE CONVERT(VARCHAR(5), (

 DATEDIFF(ss, ja.run_requested_date,

 GETDATE()) % 60

 ))

 END AS RunningForTime

 FROM msdb.dbo.sysjobactivity AS ja

 LEFT OUTER JOIN msdb.dbo.sysjobhistory AS jh ON ja.

 job_history_id = jh.instance_id

 INNER JOIN msdb.dbo.sysjobs_view AS j ON ja.job_id = j.job_id

 WHERE (

 ja.session_id = (

 SELECT MAX(session_id) AS EXPR1

 FROM msdb.dbo.sysjobactivity

 )

 )

 ) R ON R.job_id = a.Job_Id

 AND a.Running = 1

DROP TABLE #enum_job

Jobs With Execution Long Time

DECLARE @HistoryStartDate datetime

 ,@HistoryEndDate datetime

 ,@MinHistExecutions int

 ,@MinAvgSecsDuration int

SET @HistoryStartDate = '19000101'

SET @HistoryEndDate = GETDATE()

SET @MinHistExecutions = 1.0

SET @MinAvgSecsDuration = 1.0

DECLARE @currently_running_jobs TABLE (

 job_id UNIQUEIDENTIFIER NOT NULL

 ,last_run_date INT NOT NULL

 ,last_run_time INT NOT NULL

 ,next_run_date INT NOT NULL

 ,next_run_time INT NOT NULL

 ,next_run_schedule_id INT NOT NULL

 ,requested_to_run INT NOT NULL

 ,request_source INT NOT NULL

 ,request_source_id SYSNAME NULL

 ,running INT NOT NULL

 ,current_step INT NOT NULL

 ,current_retry_attempt INT NOT NULL

 ,job_state INT NOT NULL

 )

--capture details on jobs

INSERT INTO @currently_running_jobs

EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''

;WITH JobHistData AS

(

 SELECT job_id

 ,date_executed=msdb.dbo.agent_datetime(run_date, run_time)

 ,secs_duration=run_duration/10000*3600

 +run_duration%10000/100*60

 +run_duration%100

 FROM msdb.dbo.sysjobhistory

 WHERE step_id = 0 --Job Outcome

 AND run_status = 1 --Succeeded

)

,JobHistStats AS

(

 SELECT job_id

 ,AvgDuration = AVG(secs_duration*1.)

 ,AvgPlus2StDev = AVG(secs_duration*1.) + 2*stdevp(secs_duration)

 FROM JobHistData

 WHERE date_executed >= DATEADD(day, DATEDIFF(day,'19000101',@HistoryStartDate),'19000101')

 AND date_executed < DATEADD(day, 1 + DATEDIFF(day,'19000101',@HistoryEndDate),'19000101') GROUP BY job_id HAVINGCOUNT(*) >= @MinHistExecutions

 AND AVG(secs_duration*1.) >= @MinAvgSecsDuration

)

SELECT jd.job_id

 ,j.name AS [JobName]

 ,MAX(act.start_execution_date) AS [ExecutionDate]

 ,AvgDuration AS [Historical Avg Duration (secs)]

 ,AvgPlus2StDev AS [Min Threshhold (secs)]

FROM JobHistData jd

JOIN JobHistStats jhs on jd.job_id = jhs.job_id

JOIN msdb..sysjobs j on jd.job_id = j.job_id

JOIN @currently_running_jobs crj ON crj.job_id = jd.job_id

JOIN msdb..sysjobactivity AS act ON act.job_id = jd.job_id

AND act.stop_execution_date IS NULL

AND act.start_execution_date IS NOT NULL

WHERE secs_duration > AvgPlus2StDev

AND DATEDIFF(SS, act.start_execution_date, GETDATE()) > AvgPlus2StDev

AND crj.job_state = 1

GROUP BY jd.job_id, j.name, AvgDuration, AvgPlus2StDev

Select Failed Job

select J.Name,JH.Run_Status,JH.Message,

convert(datetime, rtrim(JH.run_date))

+ ((JH.run_time/10000 * 3600)

+ ((JH.run_time%10000)/100*60)

+ (JH.run_time%10000)%100) / (86399.9964 ) as run_datetime

,* from msdb..sysjobs J inner join msdb..sysjobhistory JH

on J.Job_ID = JH.job_id

--Where J.Name = 'data_mart_tab4'

where JH.Run_Status = 0