Job succeeds manually but fails if scheduled

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

Edwardteddysnips@.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|||Bryan wrote:
> teddysnips@.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|||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|||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.|||(teddysnips@.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, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||<teddysnips@.hotmail.com> wrote in message
news:1142932717.825816.151430@.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.

Job History

If you right click on job (in 2000) and do View History, Ent Mgr displays
(from what i can tell) only a certain number of lines. But I have a job that
runs every couple of minutes and I need to see much more history than that.
Is there somewhere this information is kept and is there a way I can change
the default on the number of history lines? Thx...
You should probably just query the DB. See MSDB.dbo.sysjobhistory
Ryan
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:BEC60862-A013-4509-80CD-2259731B5DEC@.microsoft.com...
> If you right click on job (in 2000) and do View History, Ent Mgr displays
> (from what i can tell) only a certain number of lines. But I have a job
that
> runs every couple of minutes and I need to see much more history than
that.
> Is there somewhere this information is kept and is there a way I can
change
> the default on the number of history lines? Thx...
|||Right click on the SQL Agent folder and choose properties. Then have a look
at the tabs and you will see a place to change the history retention
settings.
Andrew J. Kelly SQL MVP
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:BEC60862-A013-4509-80CD-2259731B5DEC@.microsoft.com...
> If you right click on job (in 2000) and do View History, Ent Mgr displays
> (from what i can tell) only a certain number of lines. But I have a job
> that
> runs every couple of minutes and I need to see much more history than
> that.
> Is there somewhere this information is kept and is there a way I can
> change
> the default on the number of history lines? Thx...

Job History

Is there a limit to the number of rows that are stored in job history?
I see where the Maintenance Plan history can be set, but not for the
individual jobs. I have set up a test job and run it over 100 times, but
there seems to be a limit of 100 entries in the sysjobhistory table.
Is this correct and if it is, where is that documented?
Thanks,
Will
Never Mind - I see that the setting is in the SQL Server Agent properties.
"Will Winn" wrote:

> Is there a limit to the number of rows that are stored in job history?
> I see where the Maintenance Plan history can be set, but not for the
> individual jobs. I have set up a test job and run it over 100 times, but
> there seems to be a limit of 100 entries in the sysjobhistory table.
> Is this correct and if it is, where is that documented?
> Thanks,
> Will

Job History

Is there a limit to the number of rows that are stored in job history?
I see where the Maintenance Plan history can be set, but not for the
individual jobs. I have set up a test job and run it over 100 times, but
there seems to be a limit of 100 entries in the sysjobhistory table.
Is this correct and if it is, where is that documented?
Thanks,
WillNever Mind - I see that the setting is in the SQL Server Agent properties.
"Will Winn" wrote:

> Is there a limit to the number of rows that are stored in job history?
> I see where the Maintenance Plan history can be set, but not for the
> individual jobs. I have set up a test job and run it over 100 times, but
> there seems to be a limit of 100 entries in the sysjobhistory table.
> Is this correct and if it is, where is that documented?
> Thanks,
> Will

Job History

If you right click on job (in 2000) and do View History, Ent Mgr displays
(from what i can tell) only a certain number of lines. But I have a job tha
t
runs every couple of minutes and I need to see much more history than that.
Is there somewhere this information is kept and is there a way I can change
the default on the number of history lines? Thx...You should probably just query the DB. See MSDB.dbo.sysjobhistory
Ryan
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:BEC60862-A013-4509-80CD-2259731B5DEC@.microsoft.com...
> If you right click on job (in 2000) and do View History, Ent Mgr displays
> (from what i can tell) only a certain number of lines. But I have a job
that
> runs every couple of minutes and I need to see much more history than
that.
> Is there somewhere this information is kept and is there a way I can
change
> the default on the number of history lines? Thx...|||Right click on the SQL Agent folder and choose properties. Then have a look
at the tabs and you will see a place to change the history retention
settings.
Andrew J. Kelly SQL MVP
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:BEC60862-A013-4509-80CD-2259731B5DEC@.microsoft.com...
> If you right click on job (in 2000) and do View History, Ent Mgr displays
> (from what i can tell) only a certain number of lines. But I have a job
> that
> runs every couple of minutes and I need to see much more history than
> that.
> Is there somewhere this information is kept and is there a way I can
> change
> the default on the number of history lines? Thx...

Job History

Is there a limit to the number of rows that are stored in job history?
I see where the Maintenance Plan history can be set, but not for the
individual jobs. I have set up a test job and run it over 100 times, but
there seems to be a limit of 100 entries in the sysjobhistory table.
Is this correct and if it is, where is that documented?
Thanks,
WillNever Mind - I see that the setting is in the SQL Server Agent properties.
"Will Winn" wrote:
> Is there a limit to the number of rows that are stored in job history?
> I see where the Maintenance Plan history can be set, but not for the
> individual jobs. I have set up a test job and run it over 100 times, but
> there seems to be a limit of 100 entries in the sysjobhistory table.
> Is this correct and if it is, where is that documented?
> Thanks,
> Will

Job History

If you right click on job (in 2000) and do View History, Ent Mgr displays
(from what i can tell) only a certain number of lines. But I have a job that
runs every couple of minutes and I need to see much more history than that.
Is there somewhere this information is kept and is there a way I can change
the default on the number of history lines? Thx...You should probably just query the DB. See MSDB.dbo.sysjobhistory
Ryan
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:BEC60862-A013-4509-80CD-2259731B5DEC@.microsoft.com...
> If you right click on job (in 2000) and do View History, Ent Mgr displays
> (from what i can tell) only a certain number of lines. But I have a job
that
> runs every couple of minutes and I need to see much more history than
that.
> Is there somewhere this information is kept and is there a way I can
change
> the default on the number of history lines? Thx...|||Right click on the SQL Agent folder and choose properties. Then have a look
at the tabs and you will see a place to change the history retention
settings.
--
Andrew J. Kelly SQL MVP
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:BEC60862-A013-4509-80CD-2259731B5DEC@.microsoft.com...
> If you right click on job (in 2000) and do View History, Ent Mgr displays
> (from what i can tell) only a certain number of lines. But I have a job
> that
> runs every couple of minutes and I need to see much more history than
> that.
> Is there somewhere this information is kept and is there a way I can
> change
> the default on the number of history lines? Thx...

job execution

Greetings.

We generate table exports on a SQL Server 2005 instance at irregular intervals. Often when exports are required we have a number of them that need to be run. We've found that the exports job run sequentially. Is there a way of simultaneously executing the jobs ?

Thanks.

alan

If you are referring to several steps in a SQL Agent Job, then they always run sequentially. You could make a separate Job for each of the steps, and, depending upon your server hardware and the user activity, they may then run in parallel.

You could create a DTS (SQL 2000) or SSIS (SQL 2005) package, and in the package you could easily extablish that the various 'steps' were to run in parallel.

|||

Very helpful.

Thank you.