Как запросить все события в заданный день, используя модель системных расписаний SQL Server?

Полностью протестировать не удалось. Это не охватывает все возможности. Например, если событие происходит каждые 3 дня, необходимо выполнить некоторые расчеты для дат, дней и т. д.

SELECT     * 
FROM       msdb.dbo.sysschedules 
WHERE   (active_start_date = 20120228) 
    OR (freq_type = 4 AND freq_interval = 1)  --every day
    OR (freq_type = 4 AND freq_interval = 14 AND datename(dw, convert(datetime, convert(varchar(8), active_start_date), 112)) = 'Tuesday') --every 14 days, starting on a Tuesday
        OR (freq_type = 8 AND ((freq_interval & 4) = 4))  -- every Tuesday
        OR (freq_type = 16 AND freq_interval = 28) -- every month on the 28th
        OR (freq_type = 32 AND freq_interval = 3 
            AND (freq_relative_interval = 8 OR freq_relative_interval = 16))  -- monthly on a Tuesday of the Fourth or Last week

Системная таблица sysjobschedules содержит информацию о том, когда выполнялось задание, и дату/ время, когда он будет запущен в следующий раз. Вы можете использовать это, чтобы узнать, когда задание будет выполняться в определенный день.

Это не без проблем; даты хранятся как целые числа и нуждаются в преобразовании, прежде чем они будут полезны, а информация обновляется только после завершения шага задания. Таким образом, вы получите результаты только для заданий, которые выполнялись хотя бы один раз.

Я нашел эту информацию в разделе Информация о выполнении задания агента SQL Server на странице Запрос информации о задании агента SQL Server

В нижней части страницы MSDN также есть некоторый контент сообщества, предоставляющий UDF для получения даты и времени из целого числа, которое вы можете использовать для декодирования дат.

ОБНОВЛЕНИЕ

Подумав о своей проблеме и осознав, что вы пытаетесь воспроизвести модель sysschedules, а не просто использовать ее, я подумал, что должен обновить свой ответ.

Ты спросил :

Как бы я переписал приведенный выше запрос, чтобы учесть все возможные комбинации freq_interval, которые могут быть вторниками?

Я создал таблицу со значениями битовой маски для дней, а затем присоединил ее к таблице расписания.

CREATE TABLE [dbo].[Schedule_Day](
    [day_name] [nchar](10) NOT NULL,
    [day_bit_value] [varbinary](50) NOT NULL
) ON [PRIMARY]

INSERT Schedule_Day(day_name, day_bit_value) VALUES ('Sunday', 1)
INSERT Schedule_Day(day_name, day_bit_value) VALUES ('Monday', 2)
INSERT Schedule_Day(day_name, day_bit_value) VALUES ('Tuesday', 4)
INSERT Schedule_Day(day_name, day_bit_value) VALUES ('Wednesday', 8)
INSERT Schedule_Day(day_name, day_bit_value) VALUES ('Thursday', 16)
INSERT Schedule_Day(day_name, day_bit_value) VALUES ('Friday', 32)
INSERT Schedule_Day(day_name, day_bit_value) VALUES ('Saturday', 64)

SELECT Schedule.schedule_id, Schedule.start_date, Schedule_Day.day_name
FROM Schedule INNER JOIN Schedule_Day 
    ON Schedule.freq_interval & Schedule_Day.day_bit_value > 0

Вот результат для расписания, работающего во вторник и четверг:

schedule_id freq_type freq_interval freq_recurrence_factor start_date
----------- --------- ------------- ---------------------- ----------
          1        32            20                      1 2012-03-06

schedule_id  start_date  day_name
-----------  ----------  --------
          1  2012-03-06  Tuesday   
          1  2012-03-06  Thursday

Однако трудная часть — это экстраполяция дат на будущее, чтобы получить все будущие вторники и четверги с месячными интервалами. Это упражнение я оставлю вам :)

Ранее я создал упрощенную систему расписаний, которую вы могли бы использовать, чтобы предоставить решение почти столь же хорошее. Вы можете имитировать расписание, которое выполняется во вторник и четверг, имея несколько записей расписания, сгруппированных каким-либо образом.

Мой дизайн расписания включал поле active_on_day, указывающее день недели, когда расписание может выполняться, поэтому job, относящееся к нескольким расписаниям, будет выполняться в эти дни. Я могу опубликовать дизайн таблицы и хранимые процедуры, если вы заинтересованы.

существует процедура sp_get_schedule_description, которая возвращает расписание в читаемом формате. Все параметры процедуры можно найти в таблице sysschedules

Так, например, я создал расписание под названием «Мое расписание1», которое содержит следующую информацию:

Recuring
Weekly
Every 2 weeks
On tuesday
Overy 12 hours Starting at 3 AM enging at 9 PM
from 1/3/2012

если я побегу

select 
freq_type, 
freq_interval,  
freq_subday_type, 
freq_subday_interval, 
freq_relative_interval,  
freq_recurrence_factor, 
active_start_date, 
active_end_date,  
active_start_time, 
active_end_time
from sysschedules where name='My Schedule1'

это даст мне

freq_type                     8
freq_interval                 5
freq_subday_type              8
freq_subday_interval          12
freq_relative_interval        0
freq_recurrence_factor        2
active_start_date             20120301          
active_end_date               99991231        
active_start_time             30000             
active_end_time               210000

если я помещу эти значения в sp_get_schedule_description следующим образом:

declare @schedule_description NVARCHAR(255)
exec sp_get_schedule_description 8, 5, 8, 12, 0, 2, 20120301, 99991231, 30000, 210000, @schedule_description output
select @schedule_description

Сделаю расписание в читаемом формате:

Every 2 week(s) on Sunday, Tuesday every 12 hour(s) between 30000 and 210000

проблема в том, что он принимает одно расписание за раз, поэтому вам нужно будет придумать какой-то цикл, чтобы вызывать его один раз для каждого задания, помещая описание в пример временной таблицы r, и вы запрашиваете временную таблицу с вашими параметрами, например, 1:

select * 
from your_table 
where description like '%Tuesday%'