October 7, 2010 at 9:39 am
Hello Everyone
I am attempting to write a query that gathers all the info about all the SQL jobs on each instance. I have most of the query, but I cannot seem to find where the information that is shown in the Job Schedule properties is located. Things like "Frequency", "Daily Frequency", etc....
Can someone give me some advice on this?
Thanks
Andrew SQLDBA
October 7, 2010 at 9:51 am
msdb..sysschedules ?
October 7, 2010 at 9:52 am
sysschedules
---------------------------------------------------------------------------------------
It begins by taking the first step.
October 7, 2010 at 10:01 am
That tell me the next time the job will be fired off, it does not tell me the frequency. Such as "Every 15 Minutes"
Is this done by a calculation?
Andrew SQLDBA
October 7, 2010 at 10:15 am
Looks to me it has what you need (But I've been wrong before :-P)
frequency is: freq_subday_interval, int
"Number of freq_subday_type periods to occur between each execution of the job."
From BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/4cac9237-7a69-4035-bb3e-928b76aad698.htm
October 7, 2010 at 10:26 am
AndrewSQLDBA (10/7/2010)
That tell me the next time the job will be fired off, it does not tell me the frequency. Such as "Every 15 Minutes"Is this done by a calculation?
Andrew SQLDBA
Yes..here is a query I wrote for a specific purpose to pull all scheduled times a job will run during a certain window, as long as the job is scheduled to run Daily (either once, or every X minutes/hours). You can see the underlying tables/etc (in combination with BOL) there and modify it to your needs.
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
AND ss.enabled = 1 -- Schedule Enabled
AND freq_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
October 7, 2010 at 10:27 am
The information is in sysschedules table. You can get this information from the documentation about freq_subday_type and freq_subday_interval in http://msdn.microsoft.com/en-us/library/ms366342.aspx
Edit: ah already answered with a script that I'll save into my utilities folder 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply