WordPress.com



/*1. Basic Job detailsThis section will produce the basic details of the job as follows:1. Job ID- Id of the Job2. Job Name- Name of the Job3. Enabled- Which will show us the job is active/enabled4. Job Owner- Owner of the Job5. LastRunDateTime- shows the last execution date and time6. LastRunStatus- shows the status of the last execution status7. LastRunDuration- shows the Duration of the last execution.(HH:MM:SS)8. NextRunDateTime- shows the next execution date and time9. LastInvokedBy- shows the User/Schedule ID that has been invoked by.10. CancelledBy- shows the User by which the job has been invoked11. Message- Output message of the Job2. Job History AnalysisThis section will fetch a history analysis result, how the job was performing for a period of time,and produce the pattern of MIN/MAX/AVG values for the Sampling as follows:1. Job ID- Id of the Job2. Job Name- Name of the Job3. Avg Duration- Average value of past executions for the sampling4. Max Duration- Maximum value of past executions for the sampling5. Min Duration- Minimum value of past executions for the sampling6. Sampling- Sampling is the number of history samples upon the calculation has been carried out.3. Job Step detailsThis section will produce the step wise details of the job as follows:1. Parent Job Name- Name of the Job.2. StepID- Step id of the job3. step_name- Name of the Step4. subsystem- Name of the subsystem used by SQL Server Agent to execute the job step5. Rundatetime- Execution Date time for the step6. Last_RunDuration- shows the Duration of the last execution. This has been provided in a more friendly way of represnting in Time format(HH:MM:SS)4. Job History detailsAs per the suggestion, included the the history info of a job for the job id passed as paremeter.1. Job ID- Id of the Job.2. Job Name- Name of the Job.3. Message- If job has succeeded, then it will give the message. If the job has failed,then the message will have detailed error message.4. run_datetime- Execution datatime.5. runduration- Duration of the job execution.6. Last_runstatus- Run status of the job execution.*****************************************************************************EDIT:13rd-Apr-2015To get the run_completed_date for the job execution*****************************************************************************Sample Executions:--To get all job informationExec sp_GetJobDetails--To get particular job informationExec sp_GetJobDetails '633AE2B6-BC60-4BD4-8685-F16FC0717033'*****************************************************************************//* Procedure Starts here*/Alter Procedure sp_GetJobDetails (@job_id uniqueidentifier = NULL)As Begin --Varibale Declarationsdeclare @num_days intdeclare @first_day datetime, @last_day datetimedeclare @first_num intif @num_days is nullset @num_days=30set @last_day = getdate()set @first_day = dateadd(dd, -@num_days, @last_day)select @first_num= cast(year(@first_day) as char(4))+replicate('0',2-len(month(@first_day)))+ cast(month(@first_day) as varchar(2))+replicate('0',2-len(day(@first_day)))+ cast(day(@first_day) as varchar(2)) --Basic Job Information ;With cteas(Select A.job_id, B.name, B.enabled, /* -- Only for later version of SQL Server 2005 */msdb.dbo.SQLAGENT_SUSER_SNAME(b.owner_sid) 'Job Owner', (Select Top 1 next_scheduled_run_date From msdb.dbo.sysjobactivity Where job_id = A.job_id Order by session_id desc) as 'NextRunDateTime',msdb.dbo.agent_datetime(last_run_date, last_run_time) as 'LastRunDateTime', Case last_run_outcomeWhen 0 Then 'Failed' When 1 Then 'Succeeded'When 2 Then 'Retry'When 3 Then 'Cancelled'Else 'NA' End Last_Run_Status, last_run_duration,casewhen (len(cast(last_run_duration as varchar(20))) < 3) then cast(last_run_duration as varchar(6))WHEN (len(cast(last_run_duration as varchar(20))) = 3) then LEFT(cast(last_run_duration as varchar(6)),1) * 60 + RIGHT(cast(last_run_duration as varchar(6)),2) WHEN (len(cast(last_run_duration as varchar(20))) = 4) then LEFT(cast(last_run_duration as varchar(6)),2) * 60 + RIGHT(cast(last_run_duration as varchar(6)),2) WHEN (len(cast(last_run_duration as varchar(20))) >= 5) then (Left(cast(last_run_duration as varchar(20)),len(last_run_duration)-4)) * 3600 +(substring(cast(last_run_duration as varchar(20)) , len(last_run_duration)-3, 2)) * 60+ Right(cast(last_run_duration as varchar(20)) , 2)End As 'Last_RunDuration',CONVERT(DATETIME, RTRIM(last_run_date)) + ((last_run_time + last_run_duration) * 9 + (last_run_time + last_run_duration) % 10000 * 6 + (last_run_time + last_run_duration) % 100 * 10) / 216e4 AS Last_RunFinishDateTime,Case last_run_outcomeWhen 1 Then Left(Replace(last_outcome_message,'The job succeeded. The Job was invoked by',''), Charindex('.',Replace(last_outcome_message,'The job succeeded. The Job was invoked by',''))) When 0 Then Left(Replace(last_outcome_message,'The job failed. The Job was invoked by',''), Charindex('.',Replace(last_outcome_message,'The job failed. The Job was invoked by',''))) When 3 Then Left(Replace(last_outcome_message,'The job was stopped prior to completion by ',''), Charindex('.',Replace(last_outcome_message,'The job was stopped prior to completion by ',''))) End 'LastInvokedBy', Case last_run_outcomeWhen 3 Then Left(Replace(last_outcome_message,'The job failed. The Job was invoked by',''), Charindex('.',Replace(last_outcome_message,'The job failed. The Job was invoked by',''))) Else ''End 'Cancelled/Stopped By', last_outcome_message 'Message' From msdb.dbo.SysJobServers A Inner Join msdb.dbo.sysjobs B on A.job_id = B.job_id Left Join msdb.dbo.sysjobschedules D on A.job_id = D.job_id Where ((A.job_id = @job_id and @Job_id is not null) OR (1=1 and @Job_id is null)) And ISNULL(last_run_date,0) <>0 And ISNULL(next_run_date,0) <>0)Select Job_id, Name as 'Job_Name', Enabled,[Job Owner],LastRunDateTime,Last_Run_Status, Right('00'+cast(Last_RunDuration/3600 as varchar(10)),2)+':'+replicate('0',2-len((Last_RunDuration % 3600)/60))+cast((Last_RunDuration % 3600)/60 as varchar(2))+':'+replicate('0',2-len((Last_RunDuration % 3600) %60))+cast((Last_RunDuration % 3600)%60 as varchar(2)) 'Last_RunDuration',Last_RunFinishDateTime,NextRunDateTime,LastInvokedBy,[Cancelled/Stopped By],MessageFrom cte Order by name -- Job Hitstory Analysis;With Cteas(selectjobhist.job_id,jobs.name,jobhist.step_id,run_dur_Casted = case when (len(cast(jobhist.run_duration as varchar(20))) < 3) then cast(jobhist.run_duration as varchar(6))WHEN (len(cast(jobhist.run_duration as varchar(20))) = 3) then LEFT(cast(jobhist.run_duration as varchar(6)),1) * 60 + RIGHT(cast(jobhist.run_duration as varchar(6)),2) WHEN (len(cast(jobhist.run_duration as varchar(20))) = 4) then LEFT(cast(jobhist.run_duration as varchar(6)),2) * 60 + RIGHT(cast(jobhist.run_duration as varchar(6)),2) WHEN (len(cast(jobhist.run_duration as varchar(20))) >= 5) then (Left(cast(jobhist.run_duration as varchar(20)),len(jobhist.run_duration)-4)) * 3600 +(substring(cast(jobhist.run_duration as varchar(20)) , len(jobhist.run_duration)-3, 2)) * 60+ Right(cast(jobhist.run_duration as varchar(20)) , 2)endfrom msdb.dbo.sysjobhistory jobhistInner Join msdb.dbo.sysjobs jobs On jobhist.job_id = jobs.job_idwherejobhist.job_id=jobs.job_idand jobhist.run_date>= @first_numand jobhist.step_id=0and ((jobs.job_id = @job_id and @Job_id is not null) OR (1=1 and @Job_id is null)) ),cte1As(Select jobs.job_id,jobs.name,'Sampling'=(select count(*) from cte jobhist where jobhist.job_id=jobs.job_id),'run_dur_max'=(select max(run_dur_Casted) from cte jobhist where jobhist.job_id=jobs.job_id),'run_dur_min'=(select min(run_dur_Casted) from cte jobhist where jobhist.job_id=jobs.job_id),'run_dur_avg'=(select avg(run_dur_Casted) from cte jobhist where jobhist.job_id=jobs.job_id)from msdb..sysjobs jobsWhere ((jobs.job_id = @job_id and @Job_id is not null) OR (1=1 and @Job_id is null)))Select job_id,name,'Avg Duration (hh:mm:ss)' = cast(run_dur_avg/3600 as varchar(10))+':'+replicate('0',2-len((run_dur_avg % 3600)/60))+cast((run_dur_avg % 3600)/60 as varchar(2))+':'+replicate('0',2-len((run_dur_avg % 3600) %60))+cast((run_dur_avg % 3600)%60 as varchar(2)),'Max Duration (hh:mm:ss)' = cast(run_dur_max/3600 as varchar(10))+':'+replicate('0',2-len((run_dur_max % 3600)/60))+cast((run_dur_max % 3600)/60 as varchar(2))+':'+replicate('0',2-len((run_dur_max % 3600) %60))+cast((run_dur_max % 3600)%60 as varchar(2)),'Min Duration (hh:mm:ss)' = cast(run_dur_min/3600 as varchar(10))+':'+replicate('0',2-len((run_dur_min % 3600)/60))+cast((run_dur_min % 3600)/60 as varchar(2))+':'+replicate('0',2-len((run_dur_min % 3600) %60))+cast((run_dur_min % 3600)%60 as varchar(2)),Samplingfrom cte1order by name --Step wise info ;With cteJobStepas ( Select B.name 'Parent Job Name',step_id,step_name,subsystem, msdb.dbo.agent_datetime(last_run_date, last_run_time) as 'RunDateTime',casewhen (len(cast(last_run_duration as varchar(20))) < 3) then cast(last_run_duration as varchar(6))WHEN (len(cast(last_run_duration as varchar(20))) = 3) then LEFT(cast(last_run_duration as varchar(6)),1) * 60 + RIGHT(cast(last_run_duration as varchar(6)),2) WHEN (len(cast(last_run_duration as varchar(20))) = 4) then LEFT(cast(last_run_duration as varchar(6)),2) * 60 + RIGHT(cast(last_run_duration as varchar(6)),2) WHEN (len(cast(last_run_duration as varchar(20))) >= 5) then (Left(cast(last_run_duration as varchar(20)),len(last_run_duration)-4)) * 3600 +(substring(cast(last_run_duration as varchar(20)) , len(last_run_duration)-3, 2)) * 60+ Right(cast(last_run_duration as varchar(20)) , 2)End As 'Last_RunDuration' ,CONVERT(DATETIME, RTRIM(last_run_date)) + ((last_run_time + last_run_duration) * 9 + (last_run_time + last_run_duration) % 10000 * 6 + (last_run_time + last_run_duration) % 100 * 10) / 216e4 AS Last_RunFinishDateTime From msdb.dbo.sysjobsteps A Inner Join msdb.dbo.sysjobs B on A.job_id = B.job_id Where ((A.job_id = @job_id and @Job_id is not null) Or (1=1 and @Job_id is null)) And ISNULL(last_run_date,0) <>0 ) Select [Parent Job Name] ,step_id,step_name,subsystem,RundateTime,Right('00'+cast(Last_RunDuration/3600 as varchar(10)),2)+':'+replicate('0',2-len((Last_RunDuration % 3600)/60))+cast((Last_RunDuration % 3600)/60 as varchar(2))+':'+replicate('0',2-len((Last_RunDuration % 3600) %60))+cast((Last_RunDuration % 3600)%60 as varchar(2)) 'Last_RunDuration',Last_RunFinishDateTime From cteJobStep Order by [Parent Job Name] --As per few suggestions from my collegues, I added History details for a job if the job id is passed. If (@job_id is not null) Begin;With CteJobHistoryAs( Select jobs.job_id,name, Case when run_status = 0 Then(Select Top 1 message From msdb.dbo.sysjobhistory AWhere A.job_id = jobs.job_id and A.run_date = jobhist.run_date and A.run_time = jobhist.run_timeand step_id = 1)Else jobhist.Message End Message,msdb.dbo.agent_datetime(run_date,run_time) run_datetime,casewhen (len(cast(run_duration as varchar(20))) < 3) then cast(run_duration as varchar(6))WHEN (len(cast(run_duration as varchar(20))) = 3) then LEFT(cast(run_duration as varchar(6)),1) * 60 + RIGHT(cast(run_duration as varchar(6)),2) WHEN (len(cast(run_duration as varchar(20))) = 4) then LEFT(cast(run_duration as varchar(6)),2) * 60 + RIGHT(cast(run_duration as varchar(6)),2) WHEN (len(cast(run_duration as varchar(20))) >= 5) then (Left(cast(run_duration as varchar(20)),len(run_duration)-4)) * 3600 +(substring(cast(run_duration as varchar(20)) , len(run_duration)-3, 2)) * 60+ Right(cast(run_duration as varchar(20)) , 2)End As 'RunDuration',CONVERT(DATETIME, RTRIM(run_date)) + ((run_time + run_duration) * 9 + (run_time + run_duration) % 10000 * 6 + (run_time + run_duration) % 100 * 10) / 216e4 AS RunFinishDateTime, Case run_statusWhen 0 Then 'Failed' When 1 Then 'Succeeded'When 2 Then 'Retry'When 3 Then 'Cancelled'Else 'NA' End Last_Run_Statusfrom msdb.dbo.sysjobhistory jobhistInner Join msdb.dbo.sysjobs jobs On jobhist.job_id = jobs.job_id Where jobs.job_id = @job_id and step_id =0)Select job_id,name,message,run_datetime,Right('00'+cast(RunDuration/3600 as varchar(10)),2)+':'+replicate('0',2-len((RunDuration % 3600)/60))+cast((RunDuration % 3600)/60 as varchar(2))+':'+replicate('0',2-len((RunDuration % 3600) %60))+cast((RunDuration % 3600)%60 as varchar(2)) 'RunDuration', RunFinishDateTimeLast_Run_Status From CteJobHistory Order by run_datetime desc EndEnd ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download