Returning full error details from SQL Server Agent jobs – Link

Posted: January 5, 2012 in SSIS
Tags: , , ,

http://www.sqlservercentral.com/articles/SQL+Server+Agent/67726/

Key Take Aways –

select * from dbo.sysjobs — All the jobs in the Server instance
select * from dbo.sysjobsteps — All the steps for the jobs
select * from dbo.Sysjobactivity — The current status of each job. This is particularly useful for seeing if a job is running, or when it last ran.
select * from dbo.sysjobhistory — The history of each job when it ran, including all job steps, until truncated (this is what you see in the Log File viewer)
select * from dbo.sysjobstepslogs — The job step log for certain jobs.
— View History on SQL Agent Job show max 1KB error message from Message column of sysjobhistory table but the sysjobstepslogs table can have 2Bn characters of log info from log column
— run_status = 0 means job step is failed

*SP code to get error message for a job step

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s