asked on

How can I get all the information about the jobs I have on a SQL server 2008

I need to create a report with all information about the jobs I have in a SQL server for example:

Job_Name Schedule Owner .....
Job1 Once a day at 3:00 am dbo

Please advise me how to created

sammySeltzer

Try this:

SELECT j.job_id, j.[name],l.[name] AS 'OwnerName' FROM msdb.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY j.[name]

Open in new window

hmra

ASKER

It looks good but it is missing the schedule time

sammySeltzer

Ok, here is *everything*, almost.

SELECT j.job_id, j.[name], l.[name] AS 'OwnerName',m.active_start_date, m.active_end_date,m.next_run_date, 
MAX(CAST(
STUFF(STUFF(CAST(jh.run_date as varchar),7,0,'-'),5,0,'-') + ' ' + 
STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') as datetime)) AS [LastRun],
CASE jh.run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END AS Status
FROM msdb.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
INNER JOIN msdb.dbo.sysjobhistory jh
ON jh.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobschedules m 
ON m.job_id = j.job_id
GROUP BY j.[name],j.job_id, jh.run_status,m.active_start_date, m.active_end_date,m.next_run_date, l.name
ORDER BY j.[name]

Open in new window

You can also add m.active_start_time, m.active_end_time to get job start and end times

sammySeltzer

I suppose you can remove job_id since you didn't ask for it:

SELECT j.[name], l.[name] AS 'OwnerName',m.active_start_date, m.active_end_date,m.next_run_date, 
MAX(CAST(
STUFF(STUFF(CAST(jh.run_date as varchar),7,0,'-'),5,0,'-') + ' ' + 
STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') as datetime)) AS [LastRun],
CASE jh.run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END AS Status
FROM msdb.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
INNER JOIN msdb.dbo.sysjobhistory jh
ON jh.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobschedules m 
ON m.job_id = j.job_id
GROUP BY j.[name], jh.run_status,m.active_start_date, m.active_end_date,m.next_run_date, l.name
ORDER BY j.[name]

Open in new window

ASKER CERTIFIED SOLUTION

sammySeltzer

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

hmra

ASKER

We are almost there!!!

active_start_date and active_end_date are not columns of the table msdb.dbo.sysjobschedules m

hmra

ASKER

Whaooo!!!

That is jus what I need thank you very much