MSSQL: find what jobs had to start during server was down

[predi]

Hi,
This is the situation:
There are around 300 jobs on the server. The server shutsdown abnormally and is not functional for let's say 2 hours. After the server is back on, I need to find what jobs had to start during the server was down.
Is there a way to get this list? I tried to write an SQL script that tells me the needed information, but the job schedule sheme is so complex that it would take too much time in developing such SQL script. Maybe anybody has a better solution, or has already made a similar script?
ThankYou.

[rmiao]

Quick way is query msdb..sysjobschedules.

[nosepicker]

Here's something I wrote that might help. First, create this function:

CREATE FUNCTION fn_calc_days
(@freq_type int)
RETURNS varchar(100)
AS
BEGIN
DECLARE @basetwo TABLE(day_value int NULL, day_name varchar(10) NULL)
DECLARE @days TABLE(day_value int NULL, day_name varchar(10) NULL)
DECLARE @day_value int,
@remainder int,
@day_name varchar(10),
@day_string varchar(100),
@table_count int,
@day_value_now int,
@counter int

INSERT INTO @basetwo VALUES(1, 'Sunday')
INSERT INTO @basetwo VALUES(2, 'Monday')
INSERT INTO @basetwo VALUES(4, 'Tuesday')
INSERT INTO @basetwo VALUES(8, 'Wednesday')
INSERT INTO @basetwo VALUES(16, 'Thursday')
INSERT INTO @basetwo VALUES(32, 'Friday')
INSERT INTO @basetwo VALUES(64, 'Saturday')

SET @remainder = @freq_type

WHILE @remainder > 0
BEGIN
SELECT @day_value = MAX(day_value) FROM @basetwo WHERE day_value <= @remainder
INSERT INTO @days(day_value) VALUES(@day_value)
SELECT @remainder = @remainder - @day_value
END

UPDATE B
SET day_name = A.day_name
FROM @basetwo A, @days B
WHERE A.day_value = B.day_value

SELECT @table_count = COUNT(*) FROM @days
SET @counter = 1
SET @day_string = ''
SET @day_value_now = 0

WHILE @counter <= @table_count
BEGIN
SELECT @day_name = day_name FROM @days WHERE day_value = (SELECT MIN(day_value) FROM @days WHERE day_value > @day_value_now)
SELECT @day_string = @day_string + ' ' + @day_name
SELECT @day_value_now = day_value FROM @days WHERE day_name = @day_name
SET @counter = @counter +1
END

SET @day_string = LTRIM(RTRIM(@day_string))

RETURN @day_string
END
GO



Then run this query:

SELECT
X.JobName, X.Enabled,
CASE WHEN X.Alert IS NOT NULL THEN 'RAISERROR ' + CONVERT(varchar, X.Alert)
ELSE LTRIM(RTRIM(REPLACE(
CASE WHEN X.MonByDayInterval = '0' THEN ''
ELSE X.MonByDayInterval END + ' ' +
CASE WHEN ISNUMERIC(ISNULL(X.FreqInterval, 0)) = 0 THEN ISNULL(X.FreqInterval, '')
ELSE '' END + ' ' +
CASE WHEN X.FreqSubdayType IS NOT NULL AND ISNUMERIC(X.FreqInterval) = 0 THEN 'every '
ELSE CASE WHEN ISNUMERIC(X.FreqInterval) = 1 THEN 'Every '
ELSE '' END END +
CASE WHEN X.FreqSubdayType = 'days' THEN
CASE WHEN X.FreqInterval = 1 THEN ''
ELSE CONVERT(varchar, ISNULL(X.FreqInterval, '')) END
ELSE CASE WHEN X.FreqSubDayNbr = 1 THEN ''
WHEN X.FreqSubDayNbr = 0 AND X.FreqSubdayType IS NULL THEN 'Once'
ELSE ISNULL(CONVERT(varchar, X.FreqSubDayNbr), '') END END + ' ' +
CASE WHEN (CONVERT(varchar, X.FreqInterval) = '1' AND X.FreqSubDayNbr = 0) OR X.FreqSubDayNbr = 1 THEN STUFF(X.FreqSubdayType, LEN(X.FreqSubdayType), 1, '')
ELSE CONVERT(varchar, ISNULL(X.FreqSubdayType, '')) END
, ' ', ' '))) END AS [Description],
X.ActiveStartTime, X.ActiveEndTime
FROM (SELECT
A.[name] AS JobName,
CASE A.enabled WHEN 1 THEN 'ENABLED' WHEN 0 THEN 'DISABLED' END AS Enabled,
CASE WHEN B.freq_type = 32 THEN
CASE B.freq_relative_interval WHEN 1 THEN 'First' WHEN 2 THEN '2nd' WHEN 4 THEN '3rd' WHEN 8 THEN '4th' WHEN 16 THEN 'Last' END
ELSE '0' END AS MonByDayInterval,
CASE B.freq_type
WHEN 4 THEN CONVERT(varchar, B.freq_interval) -- If freq_type = 4, then freq_interval = number of days
WHEN 8 THEN
CASE B.freq_interval WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 4 THEN 'Tuesday' WHEN 8 THEN 'Wednesday' WHEN 16 THEN 'Thursday' WHEN 32 THEN 'Friday' WHEN 64 THEN 'Saturday'
ELSE maintenance.dbo.fn_calc_days(B.freq_interval) END
WHEN 16 THEN CONVERT(varchar, B.freq_interval) -- If freq_type = 16, then freq_interval = date of month
WHEN 32 THEN
CASE B.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
END AS FreqInterval,
B.freq_subday_interval + B.freq_recurrence_factor AS FreqSubDayNbr,
CASE B.freq_subday_type WHEN 1 THEN
CASE WHEN B.freq_type = 4 THEN 'days' WHEN B.freq_type = 8 THEN 'weeks' WHEN B.freq_type = 16 OR B.freq_type = 32 THEN 'months' END
WHEN 4 THEN 'minutes' WHEN 8 THEN 'hours' END AS FreqSubdayType,
REVERSE(STUFF(STUFF(REVERSE(REPLICATE('0', 6 - LEN(CONVERT(varchar, B.active_start_time))) + CONVERT(varchar, B.active_start_time)), 3, 0, ':'), 6, 0, ':')) AS ActiveStartTime,
CASE B.active_end_time WHEN 235959 THEN 'n/a'
ELSE REVERSE(STUFF(STUFF(REVERSE(REPLICATE('0', 6 - LEN(CONVERT(varchar, B.active_end_time))) + CONVERT(varchar, B.active_end_time)), 3, 0, ':'), 6, 0, ':')) END AS ActiveEndTime,
C.message_id AS Alert
FROM msdb..sysjobs A
LEFT JOIN msdb..sysjobschedules B ON A.job_id = B.job_id
LEFT JOIN msdb..sysalerts C ON A.job_id = C.job_id) AS X
ORDER BY X.Enabled, X.ActiveStartTime