Finding the error from a failed job.

3

October 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:

Pic1JobFailure1

JobFailure2

JobFailure3

JobFailure4

Then we run it and it fails!

JobFailure5

And when we click on the error message we get:

JobFailure6

Well that’s not terribly useful. So let’s go look at the job history and see what we see.

JobFailure7

JobFailure8

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:

JobFailure9

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.

3 thoughts on “Finding the error from a failed job.

  1. jelmerminitab says:

    Hi, is it possible to get this error info through a query?

  2. Rich says:

    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.

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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,597 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: