September 28, 2010 at 10:25 am
Good morning. I always fear that when the SQL Servers are brought down for maintenance/patching, perhaps an important scheduled job is not running. Is there a query that can tell me jobs that did not run? Perhaps a scheduled date but no execution date or a scheduled date but no message?
Thanks for reading.
Howard
September 28, 2010 at 10:38 am
Nope..not easily anyway. You could get the schedule interval and try to calculate from that..but honestly I think that's way, way more work than it's worth. Actually that would be a cool thing to try now that I think about it...enter a start and end time and see which jobs would be scheduled to start during that period..I'll take a crack at that this afternoon.
Typically when I know a server is going down, I'll check the agent and order by Next Run Time and see if anything is going to start in my downtime window. A lot of times if something is, it's also scheduled to run every few minutes anyway and it doesn't matter if it misses a run or two.
I'll look into the schedules table/etc and see if I can come up with anything like that though and post back this afternoon.
September 28, 2010 at 10:42 am
Thanks Derrick. I poked around in msdb.dbo.sysjobhistory , msdb.dbo.sysjobs, and msdb.dbo.sysjobsteps but did not find a way to produce the result.
September 28, 2010 at 3:23 pm
Here's something that will do the trick..but there's a few caveats.
Right now it only works on jobs that are scheduled either once per day, or at an X interval of minutes. I will add Hours tomorrow sometime..or you can do it if you can figure out the code.
This will return all scheduled runs of the jobs between the times specified in the two variables at the top
DECLARE @CheckStart datetime
DECLARE @CheckEnd datetime
SET @CheckStart = '2010-09-30 00:00:00'
SET @CheckEnd = '2010-09-30 23:59:59'
DECLARE @jobruntimes TABLE ( name nvarchar(400), start_time datetime )
DECLARE @JobList TABLE (
[id] [int] IDENTITY(1,1),
[job_name] [sysname] NOT NULL,
[schedule_name] [sysname] NOT NULL,
[freq_type] [int] NOT NULL,
[freq_interval] [int] NOT NULL,
[freq_subday_type] [int] NOT NULL,
[freq_subday_interval] [int] NOT NULL,
[active_start_date] [int] NOT NULL,
[active_start_time] [int] NOT NULL
)
INSERT INTO @JobList (
job_name
, schedule_name
, freq_type
, freq_interval
, freq_subday_type
, freq_subday_interval
, active_start_date
, active_start_time
)
SELECT sj.name as job_name
, ss.name as schedule_name
, freq_type
, freq_interval
, freq_subday_type
, freq_subday_interval
, active_start_date
, active_start_time
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobschedules sjs ON sjs.job_id = sj.job_id
INNER JOIN msdb.dbo.sysschedules ss ON ss.schedule_id = sjs.schedule_id
WHERE
sj.enabled = 1 -- Job Enabled
ANDss.enabled = 1 -- Schedule Enabled
ANDfreq_type in (1,4) -- Only jobs that run Once or Daily
declare @id int
declare @jobname nvarchar(400)
declare @schedulename nvarchar(100)
declare @freqsubdaytype int
declare @freqsubdayinterval int
declare @activestartdate bigint
declare @activestarttime bigint
declare @lastiteration datetime
DECLARE gettimes CURSOR LOCAL FAST_FORWARD FOR
SELECT ID, job_name, schedule_name, freq_subday_type, freq_subday_interval, active_start_date, active_start_time FROM @JobList
OPEN GetTimes
FETCH NEXT FROM GetTimes into @id, @jobname, @schedulename, @freqsubdaytype, @freqsubdayinterval, @activestartdate, @activestarttime
WHILE @@FETCH_STATUS = 0
BEGIN
IF @freqsubdaytype = 1
BEGIN
SET @LastIteration = convert(datetime,
LEFT(convert(varchar, @checkstart, 126),10) + ' ' +
substring(convert(varchar,@activestarttime+1000000),2,2)+
':'+substring(convert(varchar,@activestarttime+1000000),4,2)+
':'+substring(convert(varchar,@activestarttime+1000000),6,2)
)
INSERT INTO @jobruntimes
SELECT @jobname,@LastIteration
END
IF @freqsubdaytype = 4
BEGIN
SET @lastiteration = (SELECT convert(datetime,
LEFT(convert(varchar, @checkstart-1, 126),10) + ' ' +
substring(convert(varchar,@activestarttime+1000000),2,2)+
':'+substring(convert(varchar,@activestarttime+1000000),4,2)+
':'+substring(convert(varchar,@activestarttime+1000000),6,2)
))
WHILE @lastiteration <= @CheckEnd
BEGIN
INSERT INTO @jobruntimes
SELECT @jobname, @lastiteration
SET @lastiteration = dateadd(mi,@freqsubdayinterval,@lastiteration)
END
END
FETCH NEXT FROM GetTimes into @id, @jobname, @schedulename, @freqsubdaytype, @freqsubdayinterval, @activestartdate, @activestarttime
END
CLOSE GetTimes
Deallocate GetTimes
select * from @jobruntimes
WHERE start_time BETWEEN @CheckStart AND @CheckEnd
ORDER BY start_time asc
edit: Updated to also check all scheduled jobs in the future during a certain time too...good for planning a window.
September 28, 2010 at 3:26 pm
Very nice. Thanks Derrick! I'll give it a run now.
Howard
September 28, 2010 at 4:02 pm
This is very valuable for planning outages. If I put in the time range for last night, can it show me which jobs did not run because the server was offline during patch/maintanance?
Thanks again!
Howard
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply