SELECT msdb.dbo.sysjobs.name, msdb.dbo.sysjobschedules.next_run_date, CASE (msdb.dbo.sysjobservers.last_run_outcome)
WHEN 0 THEN 'fehler' WHEN 1 THEN 'Erfolg' WHEN 3 THEN 'Abbruch' END AS STATUSTEXT, msdb.dbo.sysjobservers.last_run_date,
msdb.dbo.sysjobservers.last_run_time, msdb.dbo.sysjobservers.last_run_duration, msdb.dbo.sysjobs.date_modified,
CASE LEN(msdb.dbo.sysjobservers.last_run_duration)
WHEN 6 THEN LEFT(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)),2) + ':' + SUBSTRING(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)), 3,2) + ':' + RIGHT(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)), 2)
WHEN 5 THEN '0' + LEFT(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)),1) + ':' + SUBSTRING(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)), 2,2) + ':' + RIGHT(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)), 2)
WHEN 4 THEN '00' + ':' + LEFT(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)),2) + ':' + RIGHT(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)), 2)
WHEN 3 THEN '00' + ':0' + LEFT(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)),1) + ':' + RIGHT(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)),2)
WHEN 2 THEN '00' + ':00:' + RIGHT(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)),2)
WHEN 1 THEN '00' + ':00:0' + RIGHT(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)),1)
ELSE '--:--:--'
END AS last_run_durationExp,
CASE LEN(msdb.dbo.sysjobservers.last_run_time)
WHEN 6 THEN LEFT(CAST(msdb.dbo.sysjobservers.last_run_time AS VARCHAR(6)),2) + ':' + SUBSTRING(CAST(msdb.dbo.sysjobservers.last_run_time AS VARCHAR(6)), 3,2) + ':' + RIGHT(CAST(msdb.dbo.sysjobservers.last_run_time AS VARCHAR(6)), 2)
WHEN 5 THEN '0' + LEFT(CAST(msdb.dbo.sysjobservers.last_run_time AS VARCHAR(6)),1) + ':' + SUBSTRING(CAST(msdb.dbo.sysjobservers.last_run_time AS VARCHAR(6)), 2,2) + ':' + RIGHT(CAST(msdb.dbo.sysjobservers.last_run_time AS VARCHAR(6)), 2)
WHEN 4 THEN '00' + ':' + LEFT(CAST(msdb.dbo.sysjobservers.last_run_time AS VARCHAR(6)),2) + ':' + RIGHT(CAST(msdb.dbo.sysjobservers.last_run_time AS VARCHAR(6)), 2)
WHEN 3 THEN '00' + ':0' + LEFT(CAST(msdb.dbo.sysjobservers.last_run_time AS VARCHAR(6)),1) + ':' + RIGHT(CAST(msdb.dbo.sysjobservers.last_run_time AS VARCHAR(6)),2)
WHEN 2 THEN '00' + ':00:' + RIGHT(CAST(msdb.dbo.sysjobservers.last_run_time AS VARCHAR(6)),2)
WHEN 1 THEN '00' + ':00:0' + RIGHT(CAST(msdb.dbo.sysjobservers.last_run_time AS VARCHAR(6)),1)
ELSE '--:--:--'
END AS last_run_timeExp
,msdb.dbo.sysschedules.freq_type
FROM msdb.dbo.sysjobs INNER JOIN
msdb.dbo.syscategories ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id LEFT OUTER JOIN
msdb.dbo.sysoperators ON msdb.dbo.sysjobs.notify_page_operator_id = msdb.dbo.sysoperators.id LEFT OUTER JOIN
msdb.dbo.sysjobservers ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobservers.job_id LEFT OUTER JOIN
msdb.dbo.sysjobschedules ON msdb.dbo.sysjobschedules.job_id = msdb.dbo.sysjobs.job_id LEFT OUTER JOIN
msdb.dbo.sysschedules ON msdb.dbo.sysjobschedules.schedule_id = msdb.dbo.sysschedules.schedule_id
WHERE (msdb.dbo.sysschedules.freq_type in (4,8)) AND (msdb.dbo.sysjobs.name <> 'syspolicy_purge_history')
and (msdb.dbo.sysjobs.category_id <>100)
ORDER BY msdb.dbo.sysjobs.name
SELECT
SERVERPROPERTY('Servername') AS ServerName
,categories.NAME AS CategoryName
,jobs.name
,SUSER_SNAME(jobs.owner_sid) AS OwnerID
,jobs.enabled AS Enabled
,ISNULL(schedule.enabled,0) AS Scheduled
,jobs.description
,CASE WHEN jobs.description ='Dieser Auftrag ist im Besitz eines Berichtsserverprozesses. Das Ändern dieses Auftrags kann zu Datenbankinkompatibilitäten führen. Aktualisieren Sie diesen Auftrag mithilfe des Berichts-Managers oder mit Management Studio.' THEN 'Yes' ELSE 'No' END AS ReportServerJob
,CASE schedule.freq_type
WHEN 1 THEN 'Einmalig'
WHEN 4 THEN 'Täglich'
WHEN 8 THEN 'Wöchentlich'
WHEN 16 THEN 'Monatlich'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'Wenn der SQL Server-Agent gestartet wird'
WHEN 128 THEN 'Immer dann, wenn die CPU (s) im Leerlauf ist'
ELSE ''
END AS FequencyType
,CASE schedule.freq_type
WHEN 1 THEN 'O'
WHEN 4 THEN 'Jeden ' + CONVERT(VARCHAR, schedule.freq_interval) + ' Tag(e)'
WHEN 8 THEN 'Jede ' + CONVERT(VARCHAR, schedule.freq_recurrence_factor) + ' Woche(n) an ' + LEFT(CASE
WHEN schedule.freq_interval & 1 = 1
THEN 'Sonntag, '
ELSE ''
END + CASE
WHEN schedule.freq_interval & 2 = 2
THEN 'Montag, '
ELSE ''
END + CASE
WHEN schedule.freq_interval & 4 = 4
THEN 'Dienstag, '
ELSE ''
END + CASE
WHEN schedule.freq_interval & 8 = 8
THEN 'Mittwoch, '
ELSE ''
END + CASE
WHEN schedule.freq_interval & 16 = 16
THEN 'Donnerstag, '
ELSE ''
END + CASE
WHEN schedule.freq_interval & 32 = 32
THEN 'Freitag, '
ELSE ''
END + CASE
WHEN schedule.freq_interval & 64 = 64
THEN 'Samstag, '
ELSE ''
END, LEN(CASE
WHEN schedule.freq_interval & 1 = 1
THEN 'Sonntag, '
ELSE ''
END + CASE
WHEN schedule.freq_interval & 2 = 2
THEN 'Montag, '
ELSE ''
END + CASE
WHEN schedule.freq_interval & 4 = 4
THEN 'Dienstag, '
ELSE ''
END + CASE
WHEN schedule.freq_interval & 8 = 8
THEN 'Mittwoch, '
ELSE ''
END + CASE
WHEN schedule.freq_interval & 16 = 16
THEN 'Donnerstag, '
ELSE ''
END + CASE
WHEN schedule.freq_interval & 32 = 32
THEN 'Freitag, '
ELSE ''
END + CASE
WHEN schedule.freq_interval & 64 = 64
THEN 'Samstag, '
ELSE ''
END) - 1)
WHEN 16
THEN 'Tag ' + CONVERT(VARCHAR, schedule.freq_interval) + ' von jeden ' + CONVERT(VARCHAR, schedule.freq_recurrence_factor) + ' Monat(e)'
WHEN 32
THEN 'Der ' + CASE schedule.freq_relative_interval
WHEN 1
THEN 'erste'
WHEN 2
THEN 'zweite'
WHEN 4
THEN 'dritte'
WHEN 8
THEN 'vierte'
WHEN 16
THEN 'letze'
END + CASE schedule.freq_interval
WHEN 1
THEN ' Sonntag'
WHEN 2
THEN ' Montag'
WHEN 3
THEN ' Dienstag'
WHEN 4
THEN ' Mittwoch'
WHEN 5
THEN ' Donnerstag'
WHEN 6
THEN ' Freitag'
WHEN 7
THEN ' Samstag'
WHEN 8
THEN ' Tag'
WHEN 9
THEN ' Wochentag'
WHEN 10
THEN ' Wochenend Tag'
END + ' von jedem ' + CONVERT(VARCHAR, schedule.freq_recurrence_factor) + ' Monat(e)'
ELSE ''
END AS Occurence
,CASE schedule.freq_subday_type
WHEN 1
THEN 'Einmalig um ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), schedule.active_start_time), 6), 5, 0, ':'), 3, 0, ':')
WHEN 2
THEN 'immer um ' + CONVERT(VARCHAR, schedule.freq_subday_interval) + ' Sekunde(e) zwischen ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), schedule.active_start_time), 6), 5, 0, ':'), 3, 0, ':') + ' und ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), schedule.active_end_time), 6), 5, 0, ':'), 3, 0, ':')
WHEN 4
THEN 'immer1 um ' + CONVERT(VARCHAR, schedule.freq_subday_interval) + ' Minute(s) zwischen ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), schedule.active_start_time), 6), 5, 0, ':'), 3, 0, ':') + ' und ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), schedule.active_end_time), 6), 5, 0, ':'), 3, 0, ':')
WHEN 8
THEN 'immer2 um ' + CONVERT(VARCHAR, schedule.freq_subday_interval) + ' Stunde(n) zwischen ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), schedule.active_start_time), 6), 5, 0, ':'), 3, 0, ':') + ' und ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), schedule.active_end_time), 6), 5, 0, ':'), 3, 0, ':')
ELSE ''
END AS Frequency
,jobhistory.AverageDurationInSeconds AS AverageDurationSeconds
,CASE jobschedule.next_run_date
WHEN 0
THEN CONVERT(DATETIME, '1900/1/1')
ELSE CONVERT(DATETIME, CONVERT(CHAR(8), jobschedule.next_run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), jobschedule.next_run_time), 6), 5, 0, ':'), 3, 0, ':'))
END NextScheduledRunDate
,lastrunjobhistory.LastRunDate
,ISNULL(lastrunjobhistory.run_status_desc,'Unbekannt') AS run_status_desc
,jobhistory.AverageDurationInSeconds AS AverageDurationSeconds
,jobhistory.Max_Duration
,jobhistory.Num_of_Executions
,lastrunjobhistory.message
FROM msdb.dbo.sysjobs AS jobs
LEFT JOIN msdb.dbo.sysjobschedules AS jobschedule
ON jobs.job_id = jobschedule.job_id
LEFT JOIN msdb.dbo.sysschedules AS schedule
ON jobschedule.schedule_id = schedule.schedule_id
INNER JOIN msdb.dbo.syscategories categories
ON jobs.category_id = categories.category_id
LEFT OUTER JOIN (
SELECT jh.job_ID,
RTRIM(CAST(CONVERT(CHAR(2), DATEADD(ss,
MAX(CAST(SUBSTRING(CAST(run_duration + 1000000 AS VARCHAR(7)), 2, 2) AS INT)
* 60 * 60
+ CAST(SUBSTRING(CAST(run_duration + 1000000 AS VARCHAR(7)), 4, 2) AS INT)
* 60
+ CAST(SUBSTRING(CAST(run_duration + 1000000 AS VARCHAR(7)), 6, 2) AS INT)),
0), 13) - 1 AS CHAR(2))) + '.'
+ CONVERT(CHAR(8), DATEADD(ss,
MAX(CAST(SUBSTRING(CAST(run_duration
+ 1000000 AS VARCHAR(7)),
2, 2) AS INT) * 60 * 60
+ CAST(SUBSTRING(CAST(run_duration
+ 1000000 AS VARCHAR(7)),
4, 2) AS INT) * 60
+ CAST(SUBSTRING(CAST(run_duration
+ 1000000 AS VARCHAR(7)),
6, 2) AS INT)), 0), 14) Max_Duration,
RTRIM(CAST(CONVERT(CHAR(2), DATEADD(ms,
AVG(( CAST(SUBSTRING(CAST(run_duration + 1000000 AS VARCHAR(7)), 2, 2) AS INT)
* 60 * 60
+ CAST(SUBSTRING(CAST(run_duration + 1000000 AS VARCHAR(7)), 4, 2) AS INT)
* 60
+ CAST(SUBSTRING(CAST(run_duration + 1000000 AS VARCHAR(7)), 6, 2) AS INT) )
* 1000), 0), 13) - 1 AS CHAR(2)))
+ '.'
+ CONVERT(CHAR(12), DATEADD(ms,
AVG(( CAST(SUBSTRING(CAST(run_duration
+ 1000000 AS VARCHAR(7)),
2, 2) AS INT) * 60
* 60
+ CAST(SUBSTRING(CAST(run_duration
+ 1000000 AS VARCHAR(7)),
4, 2) AS INT) * 60
+ CAST(SUBSTRING(CAST(run_duration
+ 1000000 AS VARCHAR(7)),
6, 2) AS INT) )
* 1000), 0), 14) AverageDurationInSeconds,
'Num_of_Executions' = COUNT(*)
FROM msdb.dbo.sysjobhistory jh
WHERE step_id = 0
GROUP BY jh.job_ID
) AS jobhistory
ON jobhistory.job_id = jobs.job_id -- to get the average duration
LEFT OUTER JOIN
(
SELECT sysjobhist.job_id
,CASE sysjobhist.run_date
WHEN 0
THEN CONVERT(DATETIME, '1900/1/1')
ELSE CONVERT(DATETIME, CONVERT(CHAR(8), sysjobhist.run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), sysjobhist.run_time), 6), 5, 0, ':'), 3, 0, ':'))
END AS LastRunDate
,sysjobhist.run_status
,CASE sysjobhist.run_status
WHEN 0
THEN 'Failed'
WHEN 1
THEN 'Succeeded'
WHEN 2
THEN 'Retry'
WHEN 3
THEN 'Canceled'
WHEN 4
THEN 'In Progress'
ELSE 'Unknown'
END AS run_status_desc
,sysjobhist.retries_attempted
,sysjobhist.step_id
,sysjobhist.step_name
,(sysjobhist.run_duration / 10000 * 3600) + ((sysjobhist.run_duration % 10000) / 100 * 60) + (sysjobhist.run_duration % 10000) % 100 AS RunTimeInSeconds
,sysjobhist.message
,ROW_NUMBER() OVER (
PARTITION BY sysjobhist.job_id ORDER BY CASE sysjobhist.run_date
WHEN 0
THEN CONVERT(DATETIME, '1900/1/1')
ELSE CONVERT(DATETIME, CONVERT(CHAR(8), sysjobhist.run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), sysjobhist.run_time), 6), 5, 0, ':'), 3, 0, ':'))
END DESC
) AS RowOrder
FROM msdb.dbo.sysjobhistory AS sysjobhist
WHERE sysjobhist.step_id = 0 --to get just the job outcome and not all steps
)AS lastrunjobhistory
ON lastrunjobhistory.job_id = jobs.job_id -- to get the last run details
AND
lastrunjobhistory.RowOrder=1
Hinterlasse jetzt einen Kommentar