How to determine why SQL Server Agent job failed?

Question

Post reply

How to determine why SQL Server Agent job failed?

david.wheelock

Mr or Mrs. 500

Points: 574
More actions
January 11, 2010 at 1:24 pm

#229020

I have created a job in the SQL Server Agent. The job employs a ".dtsx" package to load a text file to the DB. I can run the dtsx file in the Execute Package Utility and it works just fine. However, when SQL Server Agent runs the dtsx packate the job fails with a red X. However, it gives me no clue whatsoever as to why it failed. I'm stumped.
I have tried to turn on logging, as follows, but to no avail. I opened Job Properties > Steps and opened the Job Step Properties window for the step in question. Once there, I selected Logging and created two log providers for Windows Event Log, one for the input and one for the output. However, when I confirm, close and reenter the logging page, the configuration strings that I entered are removed.
What do I need to do to find out why this job is failing? Do I turn on logging or is there some other way?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 1

If job fails it will definately writes into the log with error number. Can you provide me error number?

david.wheelock Mr or Mrs. 500 Points: 574 More actions · Answer 2

Pardon me while I dry this moisture from behind my ears. 🙂

Further research revealed to me the (Right Click) > View History function. This revealed a permission issue, which I suspected. I then researched the permission error that occurred because the sensitive information (username and login) was saved and encrypted and the job wasn't able to decrypt it. I found a reference to turning off saving sensitive info when storing the procedure from the Import and Export Data wizard. I did this and now I have the job functioning properly. So that problem is solved.

Thanks for the reply.

AmolNaik SSCarpal Tunnel Points: 4767 More actions · Answer 3

Here's what i usually do in the SQL Server Job.

1. Open the Job

2. Click Edit on the step that you want to capture the error.

3. Go to Advanced tab

4. Specify the Output file. This will be file where all your errors related to that step will be written.

Hope this helps.

Thanks!

Amol Naik

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

david.wheelock (1/12/2010)
Pardon me while I dry this moisture from behind my ears. 🙂
Further research revealed to me the (Right Click) > View History function. This revealed a permission issue, which I suspected. I then researched the permission error that occurred because the sensitive information (username and login) was saved and encrypted and the job wasn't able to decrypt it. I found a reference to turning off saving sensitive info when storing the procedure from the Import and Export Data wizard. I did this and now I have the job functioning properly. So that problem is solved.
Thanks for the reply.

Thanks for posting back with your solution.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Chuck Hottle SSCarpal Tunnel Points: 4777 More actions · Answer 5

You can also define logging to a database in the package itself. I find that useful for troubleshooting errors.