Finding the error from a failed job.
5October 7, 2015 by Kenneth Fisher
This is a fairly simple one once you’ve seen it, but then so are lots of things :). Let’s say you have a failed SQL Server job.
Here is our job that will fail:
Pic1
Then we run it and it fails!
And when we click on the error message we get:
Well that’s not terribly useful. So let’s go look at the job history and see what we see.
Well, we can see that this job failed, when it ran, and what the job name is. At the bottom is the detail information. No error though! Well, do you see that + next to the red X? Click on that and we start to get somewhere:
Now we have what we need! First we can see which step failed, and the step number. Then if you look in the detail section at the bottom we have some really useful information. Who ran this step, and what we’ve been looking for the whole time. The error. Note: If this is an SSIS step then you get the SSIS output which while it will have the error in it is going to be as cryptic as ever.
SQL Studies 
Hi, is it possible to get this error info through a query?
Look at msdb.dbo.sysjobhistory
Note that if you want to query sysjobhistory for the date and time the job ran, you have to use a system MSDB UDF to convert the values stored there (which are INT data types, amazingly!) to an actual DATETIME data type:
dbo.agent_datetime(run_date, run_time)
I learned this here: http://beyondrelational.com/modules/2/blogs/70/posts/15494/agentdatetime-function-from-msdb-database.aspx
I wasted tons of time rolling my own function to convert INTs to DATETIME before I found this little-known (to me, anyway) MS SQL function.
[…] let’s look at the GUI. If you need to look at the error for a job or it’s output, in detail, the best way is to use the View History […]
Hi, I need to find the exact error using T-SQL