Sometimes job history just won’t load in SQL Server Management Studio for one reason or another. One of the main reasons is that there are too many entries in the sysjobhistory table. The article here will help you resolve that problem. For a more immediate answer to the data you are looking for, like most things with SSMS, you can query the tables that contain this data directly.
For a permanent solution to bypassing SSMS I recommend using this stored procedure. If you just want a quick query see below.
If you want to get a job history for everything that has run over the last 7 days you can run the query below. Simply change the 7 to another number to go further back in time by days.
-- Variable Declarations
DECLARE @PreviousDate DATETIME
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @FinalDate INT
-- Initialize Variables
SET @PreviousDate = DATEADD(dd, - 7, GETDATE()) -- Last 7 days
SET @Year = DATEPART(yyyy, @PreviousDate)
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)), 2)
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)), 2)
SET @FinalDate = CAST(@Year + @Month + @Day AS INT)
-- Pull Job History
SELECT j.[name]
,s.step_name
,h.step_id
,MSDB.DBO.AGENT_DATETIME(h.run_date, h.run_time) AS run_time
,STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(h.run_duration AS VARCHAR(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS) '
,h.run_status
,h.sql_severity
,h.message
,h.SERVER
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
AND h.step_id = s.step_id
WHERE h.run_date > @FinalDate
ORDER BY h.instance_id DESC
To get a job history for everything that has succeeded or failed over the last 7 days run the query below. Simply change the @RunStatus variable to either 0 (failed) or 1 (succeeded).
-- Variable Declarations
DECLARE @RunStatus AS BIT
DECLARE @PreviousDate DATETIME
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @FinalDate INT
/*Succeeded Jobs*/
--SET @RunStatus = 1
/*Failed Jobs*/
SET @RunStatus = 0
-- Initialize Variables
SET @PreviousDate = DATEADD(dd, - 7, GETDATE()) -- Last 7 days
SET @Year = DATEPART(yyyy, @PreviousDate)
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)), 2)
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)), 2)
SET @FinalDate = CAST(@Year + @Month + @Day AS INT)
-- Pull Job History
SELECT j.[name]
,s.step_name
,h.step_id
,MSDB.DBO.AGENT_DATETIME(h.run_date, h.run_time) AS run_time
,STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(h.run_duration AS VARCHAR(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS) '
,h.run_status
,h.sql_severity
,h.message
,h.SERVER
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
AND h.step_id = s.step_id
WHERE h.run_status = @RunStatus
AND h.run_date > @FinalDate
ORDER BY h.instance_id DESC
If you want to generate a list of all the:
- jobs and their owners
- SSIS packages and their owners
you can do so by running the queries below. (If you don’t already know the precise name or ID of a job)
--Jobs
select s.name,l.name
from msdb..sysjobs s
left join master.sys.syslogins l on s.owner_sid = l.sid
--Packages
select s.name,l.name
from msdb..sysssispackages s
left join master.sys.syslogins l on s.ownersid = l.sid
Once you have retrieved either the name (command) or the ID of the job you are looking for you can plug that info into either one of the queries below also.
use msdb
select *
from dbo.sysjobsteps with (nolock)
where command like '%YourJobName%'
select *
from dbo.sysjobs sj with (nolock)
where sj.job_id = '1234-1234-1234-1234-1234'
Like this:
Like Loading...
