MSBI (SSIS/SSRS/SSAS) Online Training

Tuesday, August 18, 2009

SQL Server 2005 – Job History

Hi
Sql Server jobs are writing log info into the system tables.
If we get struck at any Job or related steps,by using the Sys.job history we can easily identify the problem is.It will give the description at high level.(success,failure,stopped all the info)

By using the following query we can get complete job info from sys tables.

SELECT a.name
,count(a.name ) as CNT
,b.[step_id]
,b.[step_name]
,b.[message]
,b.[run_date]
,b.[run_status]
,b.run_time
,b.run_duration
FROM [sysjobhistory] b JOIN sysjobs a
ON a.job_id = b.job_id

WHERE b.[run_date] ='20090101'
and b.[run_date] <='20090818'



See the below link.
http://sqlservernation.com/blogs/tipweek/archive/2009/02/25/viewing-sql-server-jobs-and-history.aspx

No comments: