SQL Agent Job Fails Immediately after Invoked

[Neurocon@gmail.com]

Hey everyone --

I've been having this problem for a couple weeks and was hoping maybe
someone can help out a little bit.

Synopsis:

This SQL agent job contains code to perform a full database backup with
verification and is writing to a local disk array. I typically also use
some code for reporting - declaring variables and inserting them into
a linked server database that I use to monitor all my database backups.


I have tested and used the code repeatedly and am confident it works.

I have ran the code through Query Analyzer and it completes with
success.

I have removed reporting code & used only the backup statement & it
fails.

I have 4 other Agent jobs doing other various tasks that function as
expected.

I have tried deleting & recreating the job & it still fails.

When I enable logging the application log has only one entry for SQL
Agent:

Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 1/4/2005
Time: 9:36:00 AM
User: N/A
Computer: ComputerName
Description:
SQL Server Scheduled Job 'DBName Full DB Backup'
(0xA4B14FB5A5DE 964B8340423E4AC 4B67B) - Status: Failed - Invoked on:
2005-01-04 09:36:00 - Message: The job failed. The Job was invoked by
User domain\UserName . The last step to run was step 1 (Step 1).

Any help or ideas would be greatly appreciated. I'm racking my brain
here and for some reason just not able to think of what the problem is.
Chadd
neurocon@gmail. com

[Simon Hayes]

Re: SQL Agent Job Fails Immediately after Invoked


<Neurocon@gmail .com> wrote in message
news:1104861142 .516518.291940@ c13g2000cwb.goo glegroups.com.. .[color=blue]
> Hey everyone --
>
> I've been having this problem for a couple weeks and was hoping maybe
> someone can help out a little bit.
>
> Synopsis:
>
> This SQL agent job contains code to perform a full database backup with
> verification and is writing to a local disk array. I typically also use
> some code for reporting - declaring variables and inserting them into
> a linked server database that I use to monitor all my database backups.
>
>
> I have tested and used the code repeatedly and am confident it works.
>
> I have ran the code through Query Analyzer and it completes with
> success.
>
> I have removed reporting code & used only the backup statement & it
> fails.
>
> I have 4 other Agent jobs doing other various tasks that function as
> expected.
>
> I have tried deleting & recreating the job & it still fails.
>
> When I enable logging the application log has only one entry for SQL
> Agent:
>
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 208
> Date: 1/4/2005
> Time: 9:36:00 AM
> User: N/A
> Computer: ComputerName
> Description:
> SQL Server Scheduled Job 'DBName Full DB Backup'
> (0xA4B14FB5A5DE 964B8340423E4AC 4B67B) - Status: Failed - Invoked on:
> 2005-01-04 09:36:00 - Message: The job failed. The Job was invoked by
> User domain\UserName . The last step to run was step 1 (Step 1).
>
> Any help or ideas would be greatly appreciated. I'm racking my brain
> here and for some reason just not able to think of what the problem is.
> Chadd
> neurocon@gmail. com
>[/color]

What does the Job History show for Step 1 (you need to click "Show step
details" in the history screen in EM)? Something that works in QA buts fails
when scheduled is often down to a permissions problem, but that's just a
guess - hopefully the step details will give more information.

Simon

[neurocon]

Re: SQL Agent Job Fails Immediately after Invoked

Simon --

Thanks for the response!

Inside job history I have something that would appear equally as
useless of an error message for step 1. I should also mention that I
have everything encapsulated into 1 step, and I've tried running the
job with the owner as "sa" as well as myself (a domain admin). There is
an entry indentical to the one below for each failure:

Result: Failed RunDuration: 00:00:00
The job failed. The Job was invoked by Schedule 18 (Default Schedule).
The last step to run was step 1 (Step 1).

[Erland Sommarskog]

Re: SQL Agent Job Fails Immediately after Invoked

neurocon (Neurocon@gmail .com) writes:[color=blue]
> Inside job history I have something that would appear equally as
> useless of an error message for step 1.[/color]

Maybe it's useless to you, but that't actually a poor excuse not to post
it. At least not if you want help with the issue.

So please check Show Step Details, and post the error message for step 1.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ryan]

Re: SQL Agent Job Fails Immediately after Invoked

Hopefully you are aware that using QA will use the permissions (and
files & config) you have and running the same job as a scheduled task
uses the permissions (and files and config) on the server it is being
run from. If not, can you test this using QA on the server, or at the
least double check for any differences between your setup and the one
of the server. My guess would be that there is some small difference
between you using this on your PC and you using this on the server.
Worth a look at least. For example, if the drive you are copying the
files to is not accessible by the admin user logged on the server.
Running through QA should give you more information.

Ryan

Neurocon@gmail. com wrote:[color=blue]
> Hey everyone --
>
> I've been having this problem for a couple weeks and was hoping maybe
> someone can help out a little bit.
>
> Synopsis:
>
> This SQL agent job contains code to perform a full database backup[/color]
with[color=blue]
> verification and is writing to a local disk array. I typically also[/color]
use[color=blue]
> some code for reporting - declaring variables and inserting them into
> a linked server database that I use to monitor all my database[/color]
backups.[color=blue]
>
>
> I have tested and used the code repeatedly and am confident it works.
>
> I have ran the code through Query Analyzer and it completes with
> success.
>
> I have removed reporting code & used only the backup statement & it
> fails.
>
> I have 4 other Agent jobs doing other various tasks that function as
> expected.
>
> I have tried deleting & recreating the job & it still fails.
>
> When I enable logging the application log has only one entry for SQL
> Agent:
>
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 208
> Date: 1/4/2005
> Time: 9:36:00 AM
> User: N/A
> Computer: ComputerName
> Description:
> SQL Server Scheduled Job 'DBName Full DB Backup'
> (0xA4B14FB5A5DE 964B8340423E4AC 4B67B) - Status: Failed - Invoked on:
> 2005-01-04 09:36:00 - Message: The job failed. The Job was invoked[/color]
by[color=blue]
> User domain\UserName . The last step to run was step 1 (Step 1).
>
> Any help or ideas would be greatly appreciated. I'm racking my brain
> here and for some reason just not able to think of what the problem[/color]
is.[color=blue]
> Chadd
> neurocon@gmail. com[/color]

[neurocon]

Re: SQL Agent Job Fails Immediately after Invoked

Erland,

I had posted the information from the "job history" it was directly
below my post and looks like this:

Result: Failed RunDuration: 00:00:00
The job failed. The Job was invoked by Schedule 18 (Default Schedule).
The last step to run was step 1 (Step 1).

This is the only information under job history, which is why I made the
reference to its lack of usefullness. If the above makes sense to you
I'm all ears, but this and the previous posting of the entry in the
system's application log are the only two messages from logging I have.
If I'm missing something let me know, but to my knowledge the agent
only logs to these two places and not inside the sql server logs. I
checked to make sure and there was nothing there either. The above
error message repeats identically each time the job is run.
Thanks for taking the time to respond.

Chadd

[neurocon]

Re: SQL Agent Job Fails Immediately after Invoked

Ryan --

Thanks for taking the time to respond. I suppose I should've specified
that I ran the code thru QA on the actual database server I'm having
the problem with. It runs great without error.

As an asside I've also toyed with the "owner" setting inside the job
properties and set it to me (domain administrator) as well as QA & the
account marked as this db's owner. All fail with same behavior.
Thanks again for the thoughts. Look forward to hearing more.

Chadd

[Erland Sommarskog]

Re: SQL Agent Job Fails Immediately after Invoked

neurocon (Neurocon@gmail .com) writes:[color=blue]
> I had posted the information from the "job history" it was directly
> below my post and looks like this:
>
> Result: Failed RunDuration: 00:00:00
> The job failed. The Job was invoked by Schedule 18 (Default Schedule).
> The last step to run was step 1 (Step 1).
>
> This is the only information under job history,[/color]

No it isn't. Check the checkbox "Show step details" which is in the
uppoer right corner of View Job History. There is usually more meat there.

(And you have been told about this checkbox a couple of times now.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[neurocon]

Re: SQL Agent Job Fails Immediately after Invoked

You area totally right. I must have been in a rush while reading
earlier and skimmed over that information. Sorry about the confusion on
my part and ultimately for wasting your time =) As you can see
working within the SQL Agent stuff certainly isn't my forte'. The
information enclosed in the other agent log gave me enough information
to resolve the problem. It did end up being a permissions issue. The
owner has been changed from myself, a Domain Admin, to my designated
domain SQL account as well as SA and the SQL account that's designated
owner of the database this job runs against but still fails. The "real"
logs you requested are below -- but I'm not sure if the "owner" of the
job is actually what I'm supposed to change as far as the job "running
under/as" this account -- that's just where I thought I'd start.

The job failed. The Job was invoked by Schedule 18 (Default Schedule).
The last step to run was step 1 (Step 1).

Executed as user: NT AUTHORITY\SYSTE M. Login failed for user 'NT
AUTHORITY\ANONY MOUS LOGON'. [SQLSTATE 28000] (Error 18456). The step
failed.

Again, thanks for your patience in my moment of ignorance. Any
information on adjusting the account for the SQL Agent job to run under
would be appreciated. I have other jobs running fine against the
database and the owner is set to the user who is marked as DBOwner in
the security area. I'm assuming I'm just not adjusting the right thing.
Thanks, in advance, for your further help.

Chadd

[Erland Sommarskog]

Re: SQL Agent Job Fails Immediately after Invoked

neurocon (Neurocon@gmail .com) writes:[color=blue]
> You area totally right. I must have been in a rush while reading
> earlier and skimmed over that information. Sorry about the confusion on
> my part and ultimately for wasting your time =) As you can see
> working within the SQL Agent stuff certainly isn't my forte'. The
> information enclosed in the other agent log gave me enough information
> to resolve the problem. It did end up being a permissions issue. The
> owner has been changed from myself, a Domain Admin, to my designated
> domain SQL account as well as SA and the SQL account that's designated
> owner of the database this job runs against but still fails. The "real"
> logs you requested are below -- but I'm not sure if the "owner" of the
> job is actually what I'm supposed to change as far as the job "running
> under/as" this account -- that's just where I thought I'd start.
>
> The job failed. The Job was invoked by Schedule 18 (Default Schedule).
> The last step to run was step 1 (Step 1).
>
> Executed as user: NT AUTHORITY\SYSTE M. Login failed for user 'NT
> AUTHORITY\ANONY MOUS LOGON'. [SQLSTATE 28000] (Error 18456). The step
> failed.
>
> Again, thanks for your patience in my moment of ignorance. Any
> information on adjusting the account for the SQL Agent job to run under
> would be appreciated. I have other jobs running fine against the
> database and the owner is set to the user who is marked as DBOwner in
> the security area. I'm assuming I'm just not adjusting the right thing.
> Thanks, in advance, for your further help.[/color]

I can't really work out if you still have a problem, or your backup
is now running as it should.

Neither can I count SQL Agent as my forté, but changing the owner
sounds like a good thing.

Notice that this is not an issue with access to the database in
question. It is the access to SQL Server as a whole that is denied.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp