Job succeeds manually but fails if scheduled

[teddysnips]

My client has a number of jobs that are run overnight. We've set them
up to email me when they're completed. Every morning I get in to a
bunch of emails like this:

<quote>

JOB RUN: 'Tech Pubs Email Notification' was run on 18/03/2006 at
00:00:00
DURATION: 0 hours, 0 minutes, 0 seconds
STATUS: Succeeded
MESSAGES: The job succeeded. The Job was invoked by Schedule 10 (Send
Mail). The last step to run was step 1 (Send Mail).
</quote>

However, the most important job - the database backup - fails every
time.

<quote>
JOB RUN: 'DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1''
was run on 20/03/2006 at 18:00:00
DURATION: 0 hours, 0 minutes, 2 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 7 (Schedule
1). The last step to run was step 1 (Step 1).
</quote>

What's strange is that the job runs successfully if you kick it off
manually (in EM: right-click and "Start Job")!!! Does anyone have any
idea of why that might be? Where to look for diagnostic information?

TIA

Edward

[Bryan]

te********@hotmail.com wrote:

My client has a number of jobs that are run overnight. We've set them
up to email me when they're completed. Every morning I get in to a
bunch of emails like this:

<quote>

JOB RUN: 'Tech Pubs Email Notification' was run on 18/03/2006 at
00:00:00
DURATION: 0 hours, 0 minutes, 0 seconds
STATUS: Succeeded
MESSAGES: The job succeeded. The Job was invoked by Schedule 10 (Send
Mail). The last step to run was step 1 (Send Mail).
</quote>

However, the most important job - the database backup - fails every
time.

<quote>
JOB RUN: 'DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1''
was run on 20/03/2006 at 18:00:00
DURATION: 0 hours, 0 minutes, 2 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 7 (Schedule
1). The last step to run was step 1 (Step 1).
</quote>

What's strange is that the job runs successfully if you kick it off
manually (in EM: right-click and "Start Job")!!! Does anyone have any
idea of why that might be? Where to look for diagnostic information?

TIA

Edward

Edward,

Have you got the right permissions?

This might sound a bit obvious but its usually the case. You might not
be the owner of the package and the system will only schedule the job
to run if the permissions are correct.

Bryan

[teddysnips]

Bryan wrote:

te********@hotmail.com wrote: [...]

Edward,

Have you got the right permissions?

This might sound a bit obvious but its usually the case. You might not
be the owner of the package and the system will only schedule the job
to run if the permissions are correct.

Bryan

I would have assumed so, but I'll check. Thanks for the suggestion.

Edward

[teddysnips]

Bryan wrote:
[...]

Edward,

Have you got the right permissions?

This might sound a bit obvious but its usually the case. You might not
be the owner of the package and the system will only schedule the job
to run if the permissions are correct.

I checked on this today. All the jobs that run correctly have exactly
the same owners/permissions as the job that fails. I can't find any
attribute of the job (apart from what it actually does, obviously) that
distinguishes it from any of the other, successful jobs.

One other thing of which I was not aware. Apparently this job had been
scheduled successfully up until about a week ago, when it began
failing.

Any further thoughts/ideas?

TIA

Edward

[Bryan]

Hi Edward,

To be honest I don't ( I hate admitting defeat though.....)

I have encountered this problem in the past especially when picking up
from any previous owners of packages.
One solution I have done is to copy the code from one package and dump
it into a package that is being allowed to run but with a different
owner.
This usually confirmed to myself that the problem must a a Owner /
Permission issue.

Apart from that I'm at a loss to recommend any other course of action.

[Erland Sommarskog]

(te********@hotmail.com) writes:

I checked on this today. All the jobs that run correctly have exactly
the same owners/permissions as the job that fails. I can't find any
attribute of the job (apart from what it actually does, obviously) that
distinguishes it from any of the other, successful jobs.

One other thing of which I was not aware. Apparently this job had been
scheduled successfully up until about a week ago, when it began
failing.

Any further thoughts/ideas?

Check View History for the job. Don't miss to check View Step History.

Unfortunately, jobs that set up from a maintenance plan does not seem to
write very much useful information, so I'm not really expecting this to
give you anything. (But check nevertheless.) I the maintenance plan has
its own log somewhere, but I don't remember where - or if there was anything
that useful in it.

May you should scrap the plan, and set up the jobs without it.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Gerry Sinkiewicz]

<te********@hotmail.com> wrote in message
news:11**********************@z34g2000cwc.googlegr oups.com...

My client has a number of jobs that are run overnight. We've set them
up to email me when they're completed. Every morning I get in to a
bunch of emails like this:

<quote>

JOB RUN: 'Tech Pubs Email Notification' was run on 18/03/2006 at
00:00:00
DURATION: 0 hours, 0 minutes, 0 seconds
STATUS: Succeeded
MESSAGES: The job succeeded. The Job was invoked by Schedule 10 (Send
Mail). The last step to run was step 1 (Send Mail).
</quote>

However, the most important job - the database backup - fails every
time.

<quote>
JOB RUN: 'DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1''
was run on 20/03/2006 at 18:00:00
DURATION: 0 hours, 0 minutes, 2 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 7 (Schedule
1). The last step to run was step 1 (Step 1).
</quote>

What's strange is that the job runs successfully if you kick it off
manually (in EM: right-click and "Start Job")!!! Does anyone have any
idea of why that might be? Where to look for diagnostic information?

TIA

Edward

If it isn't the owner of the job step (sa would be good if sa owns the
database), then check for proper disk
space in the backup destination.