All geek questions in one place

SQL Scheduled job request, last run duration?

This SQL Agent Jobs was previously used as a document to get information about all SQL Scheduled jobs.

How to find out the duration of the last run for each job? I need seconds, minutes and hours (I hope not, but I'm afraid).

Can someone give some idea on how I can request this?

+6
source share
3 answers

Assuming you don't have any tasks lasting more than 999 hours, this should give you a good starting point:

SELECT j.name, h.run_status, durationHHMMSS = STUFF(STUFF(REPLACE(STR(h.run_duration,7,0), ' ','0'),4,0,':'),7,0,':'), [start_date] = CONVERT(DATETIME, RTRIM(run_date) + ' ' + STUFF(STUFF(REPLACE(STR(RTRIM(h.run_time),6,0), ' ','0'),3,0,':'),6,0,':')) FROM msdb.dbo.sysjobs AS j INNER JOIN ( SELECT job_id, instance_id = MAX(instance_id) FROM msdb.dbo.sysjobhistory GROUP BY job_id ) AS l ON j.job_id = l.job_id INNER JOIN msdb.dbo.sysjobhistory AS h ON h.job_id = l.job_id AND h.instance_id = l.instance_id ORDER BY CONVERT(INT, h.run_duration) DESC, [start_date] DESC;
+10
source

Aaron, I made some changes to your script that include the next scheduled execution time (the start date is the start date of the start of the schedule, not necessarily the next start date of the task) and some other various checks. Thanks for the scope though; very useful starting point!

I also took some logic from oldie, but goodie ( here ).

 USE msdb Go SELECT j.Name AS 'Job Name', '"' + NULLIF(j.Description, 'No description available.') + '"' AS 'Description', SUSER_SNAME(j.owner_sid) AS 'Job Owner', (SELECT COUNT(step_id) FROM dbo.sysjobsteps WHERE job_id = j.job_id) AS 'Number of Steps', (SELECT COUNT(step_id) FROM dbo.sysjobsteps WHERE job_id = j.job_id AND command LIKE '%xp_cmdshell%') AS 'has_xpcmdshell', (SELECT COUNT(step_id) FROM dbo.sysjobsteps WHERE job_id = j.job_id AND command LIKE '%msdb%job%') AS 'has_jobstartstopupdate', (SELECT COUNT(step_id) FROM dbo.sysjobsteps WHERE job_id = j.job_id AND command LIKE '%ftp%') AS 'has_ftp', 'Job Enabled' = CASE j.Enabled WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END, 'Frequency' = CASE s.freq_type WHEN 1 THEN 'Once' WHEN 4 THEN 'Daily' WHEN 8 THEN 'Weekly' WHEN 16 THEN 'Monthly' WHEN 32 THEN 'Monthly relative' WHEN 64 THEN 'When SQLServer Agent starts' END, CASE(s.freq_subday_interval) WHEN 0 THEN 'Once' ELSE cast('Every ' + right(s.freq_subday_interval,2) + ' ' + CASE(s.freq_subday_type) WHEN 1 THEN 'Once' WHEN 4 THEN 'Minutes' WHEN 8 THEN 'Hours' END as char(16)) END as 'Subday Frequency', 'Next Start Date'= CONVERT(DATETIME, RTRIM(NULLIF(js.next_run_date, 0)) + ' ' + STUFF(STUFF(REPLACE(STR(RTRIM(js.next_run_time),6,0), ' ','0'),3,0,':'),6,0,':')), 'Max Duration' = STUFF(STUFF(REPLACE(STR(maxdur.run_duration,7,0), ' ','0'),4,0,':'),7,0,':'), 'Last Run Duration' = STUFF(STUFF(REPLACE(STR(lastrun.run_duration,7,0), ' ','0'),4,0,':'),7,0,':'), 'Last Start Date' = CONVERT(DATETIME, RTRIM(lastrun.run_date) + ' ' + STUFF(STUFF(REPLACE(STR(RTRIM(lastrun.run_time),6,0), ' ','0'),3,0,':'),6,0,':')), 'Last Run Message' = lastrun.message FROM dbo.sysjobs j LEFT OUTER JOIN dbo.sysjobschedules js ON j.job_id = js.job_id LEFT OUTER JOIN dbo.sysschedules s ON js.schedule_id = s.schedule_id LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration FROM dbo.sysjobhistory GROUP BY job_id) maxdur ON j.job_id = maxdur.job_id -- INNER JOIN -- Swap Join Types if you don't want to include jobs that have never run LEFT OUTER JOIN (SELECT j1.job_id, j1.run_duration, j1.run_date, j1.run_time, j1.message FROM dbo.sysjobhistory j1 WHERE instance_id = (SELECT MAX(instance_id) FROM dbo.sysjobhistory j2 WHERE j2.job_id = j1.job_id)) lastrun ON j.job_id = lastrun.job_id ORDER BY [Job Name]
+4
source

Script to get SQL Server Agent (work-related) information:

Script will provide us with the following information:

  • Information about setting up and setting the level of work, which is also located in the properties window of the SQL Server Agent job in SSMS.

  • Details of the last / last execution of the SQL Server Agent job, as well as the next time the job is started (if scheduled). This information can also be found in the Job Log / Activity Log window of SSMS.

  • Information on setting up and setting the Step Job Step level, which can also be found in the job step properties window in SSMS.
  • Details of the last / last execution of the task step. This information can also be found in job / log view windows in SSMS.
  • A list of schedules created / available in SQL Server and the details (origin, repetition, frequency, etc.) of each of the schedules.
 use msdb go SELECT [sJOB].[name] AS [JobName] , [sDBP].[name] AS [JobOwner] , [sCAT].[name] AS [JobCategory] , [sJOB].[description] AS [JobDescription] , [sJSTP].[step_id] AS [JobStartStepNo] , [sJSTP].[step_name] AS [JobStartStepName] , [sJOB].[date_created] AS [JobCreatedOn] , [sJOB].[date_modified] AS [JobLastModifiedOn] , CASE [sJOB].[enabled] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END AS [IsEnabled] , CASE WHEN [sSCH].[schedule_uid] IS NULL THEN 'No' ELSE 'Yes' END AS [IsScheduled] , CASE WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts' WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle' WHEN [freq_type] IN (4,8,16,32) THEN 'Recurring' WHEN [freq_type] = 1 THEN 'One Time' END [ScheduleType] , CASE [freq_type] WHEN 1 THEN 'One Time' WHEN 4 THEN 'Daily' WHEN 8 THEN 'Weekly' WHEN 16 THEN 'Monthly' WHEN 32 THEN 'Monthly - Relative to Frequency Interval' WHEN 64 THEN 'Start automatically when SQL Server Agent starts' WHEN 128 THEN 'Start whenever the CPUs become idle' END [Occurrence] , CASE [freq_type] WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)' WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' week(s) on ' + CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)' WHEN 32 THEN 'Occurs on ' + CASE [freq_relative_interval] WHEN 1 THEN 'First' WHEN 2 THEN 'Second' WHEN 4 THEN 'Third' WHEN 8 THEN 'Fourth' WHEN 16 THEN 'Last' END + ' ' + CASE [freq_interval] WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 3 THEN 'Tuesday' WHEN 4 THEN 'Wednesday' WHEN 5 THEN 'Thursday' WHEN 6 THEN 'Friday' WHEN 7 THEN 'Saturday' WHEN 8 THEN 'Day' WHEN 9 THEN 'Weekday' WHEN 10 THEN 'Weekend day' END + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)' END AS [Recurrence] , CASE [freq_subday_type] WHEN 1 THEN 'Occurs once at ' + STUFF( STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') WHEN 2 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between ' + STUFF( STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') + ' & ' + STUFF( STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') WHEN 4 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between ' + STUFF( STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') + ' & ' + STUFF( STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') WHEN 8 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between ' + STUFF( STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') + ' & ' + STUFF( STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') END [Frequency] , [sSCH].[name] AS [JobScheduleName] --,[sJSTP].database_name , Last_Run = CONVERT(DATETIME, RTRIM(run_date) + ' ' + STUFF(STUFF(REPLACE(STR(RTRIM(h.run_time),6,0), ' ','0'),3,0,':'),6,0,':')) , case [sJSTP].Last_run_outcome When 0 then 'Failed' when 1 then 'Succeeded' When 2 then 'Retry' When 3 then 'Canceled' When 5 then 'Unknown' End as Last_Run_Status ,Last_Run_Duration_HHMMSS = STUFF(STUFF(REPLACE(STR([sJSTP].last_run_duration,7,0), ' ','0'),4,0,':'),7,0,':') , Max_Duration = STUFF(STUFF(REPLACE(STR(l.run_duration,7,0), ' ','0'),4,0,':'),7,0,':') , Next_Run= CONVERT(DATETIME, RTRIM(NULLIF([sJOBSCH].next_run_date, 0)) + ' ' + STUFF(STUFF(REPLACE(STR(RTRIM([sJOBSCH].next_run_time),6,0), ' ','0'),3,0,':'),6,0,':')) , 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] , [sSVR].[name] AS [OriginatingServerName] ,[sJSTP].subsystem ,[sJSTP].command ,h.message 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] left JOIN ( SELECT job_id, instance_id = MAX(instance_id),max(run_duration) AS run_duration FROM msdb.dbo.sysjobhistory GROUP BY job_id ) AS l ON sJOB.job_id = l.job_id left JOIN msdb.dbo.sysjobhistory AS h ON h.job_id = l.job_id AND h.instance_id = l.instance_id ORDER BY [JobName]

The following is a brief description of each of the fields returned from the above query:

  • [JobName]: SQL Server Agent job name.
  • [JobOwner]: job owner.
  • [JobCategory]: the category the job belongs to, for example, a snapshot of replication, database maintenance, sending logs, etc.
  • [JobDescription]: job description.
  • [JobStartStepNo]: the number of the step from which the job was started. SQL Server allows us to take several steps as part of a job and a job can begin with what the user wants him to start with.
  • [JobStartStepName]: the name of the step from which the job is set to start.
  • [JobCreatedOn]: date and time the job was created.
  • [JobLastModifiedOn]: The date and time the job was last modified.
  • [IsEnabled]: indicator indicating whether the job is enabled or disabled.
  • [IsScheduled]: indicator indicating whether the task is scheduled or not. Jobs can be scheduled to run on a specified day (s) at a specified time, or can be called using T-SQL type code, etc.
  • [ScheduleType]: type of schedule.
  • [Appearance]: the appearance of a schedule such as Daily, Weekly, Monthly, etc.
  • [Repetition]: repetition of a schedule, such as a specific day, Special days of the week, number of weeks, etc.
  • [Frequency]: how often the task should be performed on the day (s), when it should be started, for example: Occurs only once on the scheduled day (s), Occurs every 2 hours on the appointed day, etc. between the specified start and end times.
  • [JobScheduleName]: the name of the schedule associated with the job. SQL Server allows us to associate several schedules with one task, in this case the above query returns one row for each schedule associated with each task.
  • [Last_Run]: date and time the job was completed for the last time (corresponds to the most recent run).
  • [Last_Run_Status]: state or result of the last job run.
  • [Last_Run_Duration_HHMMSS]: Duration of the last run presented in Hours: Minutes: Format of seconds.
  • [Max_Duration]: The maximum job duration was completed in Hours: Minutes: Format of seconds.
  • [Next_Run]: the date and time the job was completed next time. This information is only available for scheduled tasks (a schedule is associated with the task).
  • [JobDeletionCriterion]: criteria for deleting a job. SQL Server Agent has a function that allows us to delete / delete a task based on certain criteria, so there is no need to manually delete / clear tasks.
  • [OriginatingServerName]: the server from which the task was run.
  • [Subsystem]: type of operation, for example, integration with SQL Server Service Pack, Transact-SQL Script (T-SQL), ActiveX Script, etc.
  • [Command]: the actual command that the subsystem will execute.
  • [message]: information on success / failure of work, etc.
+2
source

Source: https://habr.com/ru/post/891750/

More articles:


All Articles