March 18, 2014 at 8:56 am
I need to add the Job Interval and Time to the following script:
SELECT DISTINCT 'CYP_BI' AS Server,Jobs.Name AS Job_Name, Jobs.description AS Alias,
'Enabled' = CASE (Jobs.enabled)
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE '??'
END,
-----------------------------------------------------------------
'Frequency' = CASE(SysSched.freq_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN
(case when (SysSched.freq_recurrence_factor > 1)
then 'Every ' + convert(varchar(3),SysSched.freq_recurrence_factor) + ' Weeks' else 'Weekly' end)
WHEN 16 THEN
(case when (SysSched.freq_recurrence_factor > 1)
then 'Every ' + convert(varchar(3),SysSched.freq_recurrence_factor) + ' Months' else 'Monthly' end)
WHEN 32 THEN 'Every ' + convert(varchar(3),SysSched.freq_recurrence_factor) + ' Months' -- RELATIVE
WHEN 64 THEN 'SQL Startup'
WHEN 128 THEN 'SQL Idle'
ELSE '??'
END,
-----------------------------------------------------------------------------
----------------------------------------------------------------
JobAct.start_execution_date AS Run_Datetime,JobAct.stop_execution_date AS Run_Endtime,
Null AS Run_Duration,
'Next Run Time' = CASE Sched.next_run_date
WHEN 0 THEN cast('n/a' as char(10))
ELSE convert(char(10), convert(datetime, convert(char(8),Sched.next_run_date)),120) + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)
END,
'Executing' AS Status
FROM msdb.dbo.sysjobs AS Jobs
INNER JOIN msdb.dbo.sysjobactivity AS JobAct ON Jobs.job_id = JobAct.job_id
INNER JOIN msdb.dbo.sysjobschedules AS Sched ON Jobs.Job_ID = Sched.job_id
INNER JOIN msdb.dbo.sysschedules AS SysSched ON Sched.schedule_id = SysSched.Schedule_id
WHERE Jobs.Name = 'Load_STG_Data'
AND JobAct.start_execution_date >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,CAST('18:00' AS DATETIME))
This query contains the job interval and time but when I add it to the first script I can't get my syntax right:
SELECT DISTINCT
'CYP_BI' AS Server,
S.name AS JobName,
S.description AS Alias,
'ScheduleName' = left(ss.name,25),
'Enabled' = CASE (S.enabled)
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE '??'
END,
'Frequency' = CASE(ss.freq_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN
(case when (ss.freq_recurrence_factor > 1)
then 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Weeks' else 'Weekly' end)
WHEN 16 THEN
(case when (ss.freq_recurrence_factor > 1)
then 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' else 'Monthly' end)
WHEN 32 THEN 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' -- RELATIVE
WHEN 64 THEN 'SQL Startup'
WHEN 128 THEN 'SQL Idle'
ELSE '??'
END,
'Interval' = CASE
WHEN (freq_type = 1) then 'One time only'
WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day'
WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days'
WHEN (freq_type = 8) then (select 'Weekly Schedule' = D1+ D2+D3+D4+D5+D6+D7
from (select ss.schedule_id,
freq_interval,
'D1' = CASE WHEN (freq_interval & 1 <> 0) then 'Sun ' ELSE '' END,
'D2' = CASE WHEN (freq_interval & 2 <> 0) then 'Mon ' ELSE '' END,
'D3' = CASE WHEN (freq_interval & 4 <> 0) then 'Tue ' ELSE '' END,
'D4' = CASE WHEN (freq_interval & 8 <> 0) then 'Wed ' ELSE '' END,
'D5' = CASE WHEN (freq_interval & 16 <> 0) then 'Thu ' ELSE '' END,
'D6' = CASE WHEN (freq_interval & 32 <> 0) then 'Fri ' ELSE '' END,
'D7' = CASE WHEN (freq_interval & 64 <> 0) then 'Sat ' ELSE '' END
from CYP_BI.msdb.dbo.sysschedules ss
where freq_type = 8
) as F
where schedule_id = sj.schedule_id
)
WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval)
WHEN (freq_type = 32) then (select freq_rel + WDAY
from (select ss.schedule_id,
'freq_rel' = CASE(freq_relative_interval)
WHEN 1 then 'First'
WHEN 2 then 'Second'
WHEN 4 then 'Third'
WHEN 8 then 'Fourth'
WHEN 16 then 'Last'
ELSE '??'
END,
'WDAY' = CASE (freq_interval)
WHEN 1 then ' Sun'
WHEN 2 then ' Mon'
WHEN 3 then ' Tue'
WHEN 4 then ' Wed'
WHEN 5 then ' Thu'
WHEN 6 then ' Fri'
WHEN 7 then ' Sat'
WHEN 8 then ' Day'
WHEN 9 then ' Weekday'
WHEN 10 then ' Weekend'
ELSE '??'
END
from CYP_BI.msdb.dbo.sysschedules ss
where ss.freq_type = 32
) as WS
where WS.schedule_id =ss.schedule_id
)
END,
'Time' = CASE (freq_subday_type)
WHEN 1 then left(stuff((stuff((replicate('0', 6 - len(Active_Start_Time)))+ convert(varchar(6),Active_Start_Time),3,0,':')),6,0,':'),8)
WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds'
WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes'
WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours'
ELSE '??'
END
,
msdb.dbo.Agent_datetime(Run_Date, Run_Time) AS Run_Datetime,
Dateadd(second, Datediff(second, 0, msdb.dbo.Agent_datetime(19000101, Run_Duration)), msdb.dbo.Agent_datetime(Run_Date, Run_Time)) AS Run_Endtime,
Stuff(Stuff(RIGHT('000000'
+ Cast(Run_Duration AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS Run_Duration,
'Next Run Time' = CASE SJ.next_run_date
WHEN 0 THEN cast('n/a' as char(10))
ELSE convert(char(10), convert(datetime, convert(char(8),SJ.next_run_date)),120) + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)
END,
CASE h.Run_Status
WHEN 0 THEN 'failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS Status
FROM CYP_BI.msdb.dbo.sysjobs S
LEFT JOIN CYP_BI.msdb.dbo.sysjobschedules SJ on S.job_id = SJ.job_id
join CYP_BI.msdb.dbo.sysschedules SS on ss.schedule_id = sj.schedule_id
JOIN CYP_BI.msdb.dbo.sysjobHistory h ON h.Job_id = s.Job_id
WHERE msdb.dbo.Agent_datetime(Run_Date, Run_Time) >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,CAST('18:00' AS DATETIME))
AND s.Name NOT LIKE 'Backup%'
AND S.Name NOT LIKE 'Cleanup%'
AND S.Name NOT LIKE 'Shrink%'
AND S.Name <> 'WMI_Disk_Space_Notification'
AND S.Name <> 'syspolicy_purge_history'
AND S.Name NOT LIKE '%Distribution%'
AND S.Name NOT LIKE '%Replication%'
AND S.enabled = 1
AND h.Step_ID = 0
Any help would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 18, 2014 at 9:45 am
SELECT DISTINCT '[linked_server]' AS Server,Jobs.Name AS Job_Name, Jobs.description AS Alias,
'Enabled' = CASE (Jobs.enabled)
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE '??'
END,
-----------------------------------------------------------------
'Frequency' = CASE(SysSched.freq_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN
(case when (SysSched.freq_recurrence_factor > 1)
then 'Every ' + convert(varchar(3),SysSched.freq_recurrence_factor) + ' Weeks' else 'Weekly' end)
WHEN 16 THEN
(case when (SysSched.freq_recurrence_factor > 1)
then 'Every ' + convert(varchar(3),SysSched.freq_recurrence_factor) + ' Months' else 'Monthly' end)
WHEN 32 THEN 'Every ' + convert(varchar(3),SysSched.freq_recurrence_factor) + ' Months' -- RELATIVE
WHEN 64 THEN 'SQL Startup'
WHEN 128 THEN 'SQL Idle'
ELSE '??'
END,
-----------------------------------------------------------------------------
----------------------------------------------------------------
JobAct.start_execution_date AS Run_Datetime,JobAct.stop_execution_date AS Run_Endtime,
Null AS Run_Duration,
'Next Run Time' = CASE Sched.next_run_date
WHEN 0 THEN cast('n/a' as char(10))
ELSE convert(char(10), convert(datetime, convert(char(8),Sched.next_run_date)),120) + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)
END,
'Executing' AS Status
, 'Interval' = CASE
WHEN (freq_type = 1) then 'One time only'
WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day'
WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days'
WHEN (freq_type = 8) then (select 'Weekly Schedule' = D1+ D2+D3+D4+D5+D6+D7
from (select SysSched.schedule_id,
freq_interval,
'D1' = CASE WHEN (freq_interval & 1 <> 0) then 'Sun ' ELSE '' END,
'D2' = CASE WHEN (freq_interval & 2 <> 0) then 'Mon ' ELSE '' END,
'D3' = CASE WHEN (freq_interval & 4 <> 0) then 'Tue ' ELSE '' END,
'D4' = CASE WHEN (freq_interval & 8 <> 0) then 'Wed ' ELSE '' END,
'D5' = CASE WHEN (freq_interval & 16 <> 0) then 'Thu ' ELSE '' END,
'D6' = CASE WHEN (freq_interval & 32 <> 0) then 'Fri ' ELSE '' END,
'D7' = CASE WHEN (freq_interval & 64 <> 0) then 'Sat ' ELSE '' END
from msdb.dbo.sysschedules ss
where freq_type = 8
) as F
where schedule_id = sched.schedule_id
)
WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval)
WHEN (freq_type = 32) then (select freq_rel + WDAY
from (select SysSched.schedule_id,
'freq_rel' = CASE(freq_relative_interval)
WHEN 1 then 'First'
WHEN 2 then 'Second'
WHEN 4 then 'Third'
WHEN 8 then 'Fourth'
WHEN 16 then 'Last'
ELSE '??'
END,
'WDAY' = CASE (freq_interval)
WHEN 1 then ' Sun'
WHEN 2 then ' Mon'
WHEN 3 then ' Tue'
WHEN 4 then ' Wed'
WHEN 5 then ' Thu'
WHEN 6 then ' Fri'
WHEN 7 then ' Sat'
WHEN 8 then ' Day'
WHEN 9 then ' Weekday'
WHEN 10 then ' Weekend'
ELSE '??'
END
from msdb.dbo.sysschedules ss
where SysSched.freq_type = 32
) as WS
where WS.schedule_id =SysSched.schedule_id
)
END,
'Time' = CASE (freq_subday_type)
WHEN 1 then left(stuff((stuff((replicate('0', 6 - len(Active_Start_Time)))+ convert(varchar(6),Active_Start_Time),3,0,':')),6,0,':'),8)
WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds'
WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes'
WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours'
ELSE '??'
END
,
msdb.dbo.Agent_datetime(Run_Date, Run_Time) AS Run_Datetime,
Dateadd(second, Datediff(second, 0, msdb.dbo.Agent_datetime(19000101, Run_Duration)), msdb.dbo.Agent_datetime(Run_Date, Run_Time)) AS Run_Endtime,
Stuff(Stuff(RIGHT('000000'
+ Cast(Run_Duration AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS Run_Duration
FROM msdb.dbo.sysjobs AS Jobs
INNER JOIN msdb.dbo.sysjobactivity AS JobAct ON Jobs.job_id = JobAct.job_id
INNER JOIN msdb.dbo.sysjobschedules AS Sched ON Jobs.Job_ID = Sched.job_id
INNER JOIN msdb.dbo.sysschedules AS SysSched ON Sched.schedule_id = SysSched.Schedule_id
JOIN msdb.dbo.sysjobHistory h ON h.Job_id = Jobs.Job_id
WHERE Jobs.Name like '0%'--= 'Load_STG_Data'
AND JobAct.start_execution_date >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,CAST('18:00' AS DATETIME))
Try this... Perhaps you need to filter the History table a bit, but this would get you started.
March 18, 2014 at 10:51 am
Thanks.
I need it to return 1 record for each job that is currently executing.
The following code does not work for In Progress.
CASE h.Run_Status
WHEN 0 THEN 'failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS Status
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 18, 2014 at 4:17 pm
I think this solution is close to what you want.
SELECT DISTINCT
Jobs.NAME AS Job_Name
, Jobs.description AS Alias
, 'Enabled' = CASE (Jobs.enabled)
WHEN 0
THEN 'No'
WHEN 1
THEN 'Yes'
ELSE '??'
END
,
-----------------------------------------------------------------
'Frequency' = CASE (SysSched.freq_type)
WHEN 1
THEN 'Once'
WHEN 4
THEN 'Daily'
WHEN 8
THEN (
CASE
WHEN (SysSched.freq_recurrence_factor > 1)
THEN 'Every ' + convert(VARCHAR(3), SysSched.freq_recurrence_factor) + ' Weeks'
ELSE 'Weekly'
END
)
WHEN 16
THEN (
CASE
WHEN (SysSched.freq_recurrence_factor > 1)
THEN 'Every ' + convert(VARCHAR(3), SysSched.freq_recurrence_factor) + ' Months'
ELSE 'Monthly'
END
)
WHEN 32
THEN 'Every ' + convert(VARCHAR(3), SysSched.freq_recurrence_factor) + ' Months' -- RELATIVE
WHEN 64
THEN 'SQL Startup'
WHEN 128
THEN 'SQL Idle'
ELSE '??'
END
, CASE his.Run_Status
WHEN 0 THEN 'failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS Status
--,
-------------------------------------------------------------------------------
------------------------------------------------------------------
--JobAct.start_execution_date AS Run_Datetime
--, JobAct.stop_execution_date AS Run_Endtime
--, NULL AS Run_Duration
, 'Next Run Time' = CASE Sched.next_run_date
WHEN 0
THEN cast('n/a' AS CHAR(10))
ELSE convert(CHAR(10), convert(DATETIME, convert(CHAR(8), Sched.next_run_date)), 120) + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time))) + convert(VARCHAR(6), next_run_time), 3, 0, ':')), 6, 0, ':'), 8)
END
, 'Executing' AS STATUS
, 'Interval' = CASE
WHEN (freq_type = 1)
THEN 'One time only'
WHEN (
freq_type = 4
AND freq_interval = 1
)
THEN 'Every Day'
WHEN (
freq_type = 4
AND freq_interval > 1
)
THEN 'Every ' + convert(VARCHAR(10), freq_interval) + ' Days'
WHEN (freq_type = 8)
THEN (
SELECT 'Weekly Schedule' = D1 + D2 + D3 + D4 + D5 + D6 + D7
FROM (
SELECT SysSched.schedule_id
, freq_interval
, 'D1' = CASE
WHEN (freq_interval & 1 <> 0)
THEN 'Sun '
ELSE ''
END
, 'D2' = CASE
WHEN (freq_interval & 2 <> 0)
THEN 'Mon '
ELSE ''
END
, 'D3' = CASE
WHEN (freq_interval & 4 <> 0)
THEN 'Tue '
ELSE ''
END
, 'D4' = CASE
WHEN (freq_interval & 8 <> 0)
THEN 'Wed '
ELSE ''
END
, 'D5' = CASE
WHEN (freq_interval & 16 <> 0)
THEN 'Thu '
ELSE ''
END
, 'D6' = CASE
WHEN (freq_interval & 32 <> 0)
THEN 'Fri '
ELSE ''
END
, 'D7' = CASE
WHEN (freq_interval & 64 <> 0)
THEN 'Sat '
ELSE ''
END
FROM msdb.dbo.sysschedules ss
WHERE freq_type = 8
) AS F
WHERE schedule_id = sched.schedule_id
)
WHEN (freq_type = 16)
THEN 'Day ' + convert(VARCHAR(2), freq_interval)
WHEN (freq_type = 32)
THEN (
SELECT freq_rel + WDAY
FROM (
SELECT SysSched.schedule_id
, 'freq_rel' = CASE (freq_relative_interval)
WHEN 1
THEN 'First'
WHEN 2
THEN 'Second'
WHEN 4
THEN 'Third'
WHEN 8
THEN 'Fourth'
WHEN 16
THEN 'Last'
ELSE '??'
END
, 'WDAY' = CASE (freq_interval)
WHEN 1
THEN ' Sun'
WHEN 2
THEN ' Mon'
WHEN 3
THEN ' Tue'
WHEN 4
THEN ' Wed'
WHEN 5
THEN ' Thu'
WHEN 6
THEN ' Fri'
WHEN 7
THEN ' Sat'
WHEN 8
THEN ' Day'
WHEN 9
THEN ' Weekday'
WHEN 10
THEN ' Weekend'
ELSE '??'
END
FROM msdb.dbo.sysschedules ss
WHERE SysSched.freq_type = 32
) AS WS
WHERE WS.schedule_id = SysSched.schedule_id
)
END
, 'Time' = CASE (freq_subday_type)
WHEN 1
THEN left(stuff((stuff((replicate('0', 6 - len(Active_Start_Time))) + convert(VARCHAR(6), Active_Start_Time), 3, 0, ':')), 6, 0, ':'), 8)
WHEN 2
THEN 'Every ' + convert(VARCHAR(10), freq_subday_interval) + ' seconds'
WHEN 4
THEN 'Every ' + convert(VARCHAR(10), freq_subday_interval) + ' minutes'
WHEN 8
THEN 'Every ' + convert(VARCHAR(10), freq_subday_interval) + ' hours'
ELSE '??'
END
, msdb.dbo.Agent_datetime(Run_Date, Run_Time) AS Run_Datetime
, Dateadd(second, Datediff(second, 0, msdb.dbo.Agent_datetime(19000101, Run_Duration)), msdb.dbo.Agent_datetime(Run_Date, Run_Time)) AS Run_Endtime
, Stuff(Stuff(RIGHT('000000' + Cast(Run_Duration AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS Run_Duration
FROM msdb.dbo.sysjobs AS Jobs
INNER JOIN msdb.dbo.sysjobactivity AS JobAct
ON Jobs.job_id = JobAct.job_id
INNER JOIN msdb.dbo.sysjobschedules AS Sched
ON Jobs.Job_ID = Sched.job_id
INNER JOIN msdb.dbo.sysschedules AS SysSched
ON Sched.schedule_id = SysSched.Schedule_id
JOIN (select job_id, max(msdb.dbo.Agent_datetime(Run_Date, Run_Time)) AS Run_Datetime
from msdb.dbo.sysjobHistory
where step_id = 0
group by job_id) max_his
ON max_his.Job_id = Jobs.Job_id
JOIN msdb.dbo.sysjobHistory his
ON his.Job_id = Jobs.Job_id
AND msdb.dbo.Agent_datetime(his.Run_Date, his.Run_Time) = max_his.Run_Datetime
WHERE Jobs.NAME LIKE '0%' --= 'Load_STG_Data'
AND JobAct.start_execution_date >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) - 1, CAST('18:00' AS DATETIME))
and his.step_id = 0
March 18, 2014 at 4:23 pm
Unfortunately it does not return any records.
Thank you for your help.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 19, 2014 at 5:55 am
Adjust the WHERE clause to your own needs. I have chaged it (filter on name) to work in my own environment. It also filters to only display the jobs that just recently had run.
March 19, 2014 at 7:24 am
HanShi (3/19/2014)
Adjust the WHERE clause to your own needs. I have chaged it (filter on name) to work in my own environment. It also filters to only display the jobs that just recently had run.
Thanks but I already did that.
Execute the first query on my original post while there is at least one job
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 19, 2014 at 8:23 am
When I remove the complete WHERE clause, I get results displaying the last execution of every job. Each job is displayed the number of times equal to the number of steps for each job. So from my point of view the query runs fine.
If you don't get results try executing only several parts of the query. Start with running only the subselect (max_his). Next run the query removing some of the joined tables and see when the results appear. Then you know which join is causing the trouble.
March 19, 2014 at 9:18 am
I currently have this code which works. It shows all of the jobs that are running but have not completed:
SELECT DISTINCT 'CYP_BI' AS Server,Jobs.Name AS Job_Name, Jobs.description AS Alias,
'Enabled' = CASE (Jobs.enabled)
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE '??'
END,
-----------------------------------------------------------------
'Frequency' = CASE(SysSched.freq_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN
(case when (SysSched.freq_recurrence_factor > 1)
then 'Every ' + convert(varchar(3),SysSched.freq_recurrence_factor) + ' Weeks' else 'Weekly' end)
WHEN 16 THEN
(case when (SysSched.freq_recurrence_factor > 1)
then 'Every ' + convert(varchar(3),SysSched.freq_recurrence_factor) + ' Months' else 'Monthly' end)
WHEN 32 THEN 'Every ' + convert(varchar(3),SysSched.freq_recurrence_factor) + ' Months' -- RELATIVE
WHEN 64 THEN 'SQL Startup'
WHEN 128 THEN 'SQL Idle'
ELSE '??'
END,
----------------------------------------------------------------
JobAct.start_execution_date AS Run_Datetime,JobAct.stop_execution_date AS Run_Endtime,
Null AS Run_Duration,
'Next Run Time' = CASE Sched.next_run_date
WHEN 0 THEN cast('n/a' as char(10))
ELSE convert(char(10), convert(datetime, convert(char(8),Sched.next_run_date)),120) + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)
END,
'Executing' AS Status
FROM msdb.dbo.sysjobs AS Jobs
INNER JOIN msdb.dbo.sysjobactivity AS JobAct ON Jobs.job_id = JobAct.job_id
INNER JOIN msdb.dbo.sysjobschedules AS Sched ON Jobs.Job_ID = Sched.job_id
INNER JOIN msdb.dbo.sysschedules AS SysSched ON Sched.schedule_id = SysSched.Schedule_id
WHERE Jobs.Name = 'Load_STG_Data'
AND JobAct.start_execution_date >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,CAST('18:00' AS DATETIME))
ServerJob_NameAliasEnabledFrequencyRun_DatetimeRun_EndtimeRun_DurationNext Run TimeStatus
CYP_BILoad_STG_DataLoad_STG_Data YesDaily2014-03-19 11:05:46.000NULLNULL2014-03-20 07:00:00Executing
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply