December 8, 2006 at 10:22 am
Apologies admins for the double posting!
Hello all, Im working on a query to make db admin easier for me (ignore all the substrings!)
select distinct j.Name collate Latin1_General_CI_AS as 'Job Name',
'Internal Live Server' as 'Server Name',
CONVERT(VARCHAR(25),CAST((
SUBSTRING((Convert(varchar(10),h.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(h.run_time as varchar(6)))) + CAST(h.run_time as varchar(6))),1,4)
+ '/' +
SUBSTRING((Convert(varchar(10),h.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(h.run_time as varchar(6)))) + CAST(h.run_time as varchar(6))),5,2)
+ '/' +
SUBSTRING((Convert(varchar(10),h.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(h.run_time as varchar(6)))) + CAST(h.run_time as varchar(6))),7,2)
+ ' ' +
SUBSTRING((Convert(varchar(10),h.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(h.run_time as varchar(6)))) + CAST(h.run_time as varchar(6))),9,2)
+ ':' +
SUBSTRING((Convert(varchar(10),h.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(h.run_time as varchar(6)))) + CAST(h.run_time as varchar(6))),11,2)
+ ':' +
SUBSTRING((Convert(varchar(10),h.run_date) + REPLICATE('0', 6 - DATALENGTH(CAST(h.run_time as varchar(6)))) + CAST(h.run_time as varchar(6))),13,2)
) as datetime)) as 'Last Run Date',
case h.run_status
when 0 then 'Failed'
when 1 then 'Successful'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as 'Job Status'
from msdb.dbo.sysJobHistory h, msdb.dbo.sysJobs j
where j.job_id = h.job_id and h.run_date =
(select max(hi.run_date) from msdb.dbo.sysJobHistory hi where h.job_id = hi.job_id)
This returns an output of all jobs that have run and their last run status as follows
DB Backup Job for DB Maintenance Plan 'All User DB' Internal Live Server Dec 7 2006 7:45PM Successful
DB Backup Job for DB Maintenance Plan 'System DB' Internal Live Server Dec 7 2006 7:00PM Successful
Optimizations Job for DB Maintenance Plan 'All User DB' Internal Live Server Dec 7 2006 7:15PM Successful
Optimizations Job for DB Maintenance Plan 'System DB' Internal Live Server Dec 3 2006 1:00AM Successful
ServerSpaceCheck Internal Live Server Dec 8 2006 1:34PM Successful
ServerSpaceCheck Internal Live Server Dec 8 2006 2:00PM Successful
Transaction Log Backup Job for DB Maintenance Plan 'Transaction Log Backup' Internal Live Server Dec 8 2006 1:00PM Successful
Transaction Log Backup Job for DB Maintenance Plan 'Transaction Log Backup' Internal Live Server Dec 8 2006 2:00PM Successful
Only problem is if a job is run more than once in a day such as the ServerSpaceCheck and Trans Log backups it returns all these jobs for the day as the query is filtering on max(run_date). I need it to filter on max(run_date) and the max(run_time) in order to get the last job run time but i am having great dificulty! by the way i hate the sysjobshistory table design hence all the substrings above to get the Date and time put together in a user friendly way!
The original query is actually referencing a linked server using the server.database.owner.object format but I have stripped this in order to make the it easier to visualise. you can simply copy and paste the above script into any server.
Please HELP!
Sizla
December 8, 2006 at 10:49 am
This got double posted.
Follow this link to go to the original question.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=329199
Consider this thread closed.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply