Просмотр задач на серверах MSSQL Server запросом

Административные скрипты MS SQL > Просмотр задач на серверах MSSQL Server запросом
08.09.2015 13:06:49


Наиболее часто встречающиеся слова в статье:

[VARCHAR] [''000000''] [CONVERT] [active_start_time] [convert] [varchar] [active_end_time] [SUBSTRING] [freq_interval] [freq_type]


Статья:

Просмотр задач на серверах MSSQL Server  запросом

-- задачи >2005 sqlsqerver с датами и временем начала
SELECT
j.Name AS JobName
, c.Name AS Category
, CASE j.enabled WHEN 1 THEN 'Yes' else 'No' END as Enabled
, CASE s.enabled WHEN 1 THEN 'Yes' else 'No' END as Scheduled
, j.Description
, 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 SQL Server Agent starts'
WHEN 128 THEN 'Start whenever the CPU(s) become idle' END as Occurs
, CASE s.freq_type
WHEN 1 THEN 'O'
WHEN 4 THEN 'Every '
+ convert(varchar,s.freq_interval)
+ ' day(s)'
WHEN 8 THEN 'Every '
+ convert(varchar,s.freq_recurrence_factor)
+ ' weeks(s) on '
+ case s.freq_interval
when 1 THEN 'воскресенье'
when 2 THEN 'Понедельник'
when 4 THEN 'Вторник'
when 8 THEN 'Среда'
when 16 THEN 'четверг'
when 32 THEN 'пятница'
when 64 THEN 'суббота'
when 127 THEN 'Пн,Вт,Ср,Чт,Пт,Сб,Вс'
end
WHEN 16 THEN 'Day ' + convert(varchar,s.freq_interval)
+ ' of every '
+ convert(varchar,s.freq_recurrence_factor)
+ ' month(s)'
WHEN 32 THEN 'The '
+ CASE s.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 s.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 '
+ convert(varchar,s.freq_recurrence_factor)
+ ' month(s)' END AS Occurs_detail
, CASE s.freq_subday_type
WHEN 1 THEN 'Occurs once at '
+ convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),2))
WHEN 2 THEN 'Occurs every '
+ convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),2))
+ ' Seconds(s) Starting at '
+ convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),2))
+ ' ending at '
+ convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,s.active_end_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,s.active_end_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,s.active_end_time),6),2))
WHEN 4 THEN 'Occurs every '
+ convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),2))
+ ' Minute(s) Starting at '
+ convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),2))
+ ' ending at '
+ convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,s.active_end_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,s.active_end_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,s.active_end_time),6),2))
WHEN 8 THEN 'Occurs every '
+ convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),2))
+ ' Hour(s) Starting at '
+ convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),2))
+ ' ending at '
+ convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,s.active_end_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,s.active_end_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,s.active_end_time),6),2)) END AS Frequency
, CASE WHEN s.freq_type = 1 THEN 'On date: '
+ convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),2))
+ ' At time: ' + convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,s.active_end_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,s.active_end_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,s.active_end_time),6),2))
+ convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),2))
WHEN s.freq_type < 64 THEN 'Start time: '
+ convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,s.active_start_time),6),2))
+ ' end date: ' + convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,s.active_end_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,s.active_end_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,s.active_end_time),6),2))
end as Duration

FROM msdb.dbo.sysjobs j (NOLOCK)
INNER JOIN msdb.dbo.sysjobschedules js (NOLOCK) ON j.job_id = js.job_id
INNER JOIN msdb.dbo.sysschedules s (NOLOCK) ON js.schedule_id = s.schedule_id
INNER JOIN msdb.dbo.syscategories c (NOLOCK) ON j.category_id = c.category_id



ORDER BY j.name

 

-- задачи 2000 sqlsqerver с датами и временем начала

--Работает только на 2000 sql

 

use msdb
go
SELECT h.[originating_server]
,h.[name]
,h2.[step_name]
,round( (cast(substring(right('00000000'+ cast(MIN(h3.run_duration ) AS VARCHAR(16)), 8), 1, 4) as int) * 60)+cast(substring(right('00000000'+ cast(MIN(h3.run_duration ) AS VARCHAR(16)), 8), 5, 2) as int) + cast(substring( right('00000000'+ cast(MIN(h3.run_duration ) AS VARCHAR(16)), 8), 7, 2) as float) / 60, 2) AS MIN_run_duration
,round( (cast(substring(right('00000000'+ cast(MAX(h3.run_duration ) AS VARCHAR(16)), 8), 1, 4) as int) * 60)+cast(substring(right('00000000'+ cast(MAX(h3.run_duration ) AS VARCHAR(16)), 8), 5, 2) as int) + cast(substring( right('00000000'+ cast(MAX(h3.run_duration ) AS VARCHAR(16)), 8), 7, 2) as float) / 60, 2) AS MAX_run_duration

,h4.freq_interval
,h4.freq_type
,SUBSTRING(cast(h4.active_start_time AS VARCHAR(10)),1, LEN(cast(h4.active_start_time AS VARCHAR(10)))-2) as [START_TIME (hhmm)]

,[Период freq_type]=case
WHEN h4.freq_type=4 THEN 'Каждый день в '
WHEN h4.freq_type=1 THEN 'Однократно'
WHEN h4.freq_type=8 THEN 'Еженедельно'
WHEN h4.freq_type=16 THEN 'Ежемесячно'
WHEN h4.freq_type=32 THEN 'Monthly relative'
WHEN h4.freq_type=64 THEN 'When SQL Server
Agent starts' end
,Occurs_detail=CASE h4.freq_type
WHEN 1 THEN 'O'
WHEN 4 THEN ' Каждый ' + convert(varchar,h4.freq_interval)
+ ' день(s)'
WHEN 8 THEN 'Каждый ' + convert(varchar,h4.freq_recurrence_factor)
+ ' неделя(s) '
+ case h4.freq_interval
when 1 THEN 'воскресенье'
when 2 THEN 'Понедельник'
when 4 THEN 'Вторник'
when 8 THEN 'Среда'
when 16 THEN 'четверг'
when 32 THEN 'пятница'
when 64 THEN 'суббота'
when 127 THEN 'Пн,Вт,Ср,Чт,Пт,Сб,Вс'
end
WHEN 16 THEN 'День ' + convert(varchar,h4.freq_interval)
+ 'из каждых '
+ convert(varchar,h4.freq_recurrence_factor)
+ ' месяц(s)'
WHEN 32 THEN 'The '
+ CASE h4.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 h4.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 '
+ convert(varchar,h4.freq_recurrence_factor)
+ ' month(s)' END

,CASE h4.freq_subday_type
WHEN 1 THEN 'Происходит раз ' +convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,h4.active_start_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,h4.active_start_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,h4.active_start_time),6),2))
WHEN 2 THEN 'Происходит каждый '
+ convert(varchar,h4.freq_subday_interval)
+ ' Seconds(s) Starting at ' +convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,h4.active_start_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,h4.active_start_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,h4.active_start_time),6),2))
+ ' ending at ' +convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,h4.active_end_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,h4.active_end_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,h4.active_end_time),6),2))
WHEN 4 THEN 'Происходит каждые '
+ convert(varchar,h4.freq_subday_interval)
+ ' Minute(s) Starting at ' +convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,h4.active_start_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,h4.active_start_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,h4.active_start_time),6),2))
+ ' ending at ' + convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,h4.active_end_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,h4.active_end_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,h4.active_end_time),6),2))
WHEN 8 THEN 'Происходит каждые '
+ convert(varchar,h4.freq_subday_interval)
+ ' Hour(s) Starting at ' +convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,h4.active_start_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,h4.active_start_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,h4.active_start_time),6),2))
+ ' ending at ' +convert(varchar,LEFT(RIGHT('000000' + CONVERT(VARCHAR,h4.active_end_time),6),2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,h4.active_end_time),6),3,2) + ':' + RIGHT(RIGHT('000000' + CONVERT(VARCHAR,h4.active_end_time),6),2))
END AS [Frequency]


FROM [sysjobs] h (NOLOCK)
join sysjobsteps h2 (NOLOCK) ON h2.job_id=h.job_id
JOIN sysjobhistory h3 (NOLOCK) ON h3.job_id=h.job_id AND h3.step_id = h2.step_id
JOIN sysjobschedules h4 (NOLOCK) ON h4.job_id=h.job_id
WHERE h.[enabled]=1
GROUP BY h.[originating_server]
,h.[name]
,h2.[step_name]
,h4.freq_type
,h4.freq_interval
,h4.active_start_time
,h4.freq_recurrence_factor
,h4.freq_relative_interval
,h4.active_start_time
,h4.freq_subday_interval
,h4.freq_subday_type
,h4.active_end_time
go