I have a job which runs on one of our servers overnight. For 3 months
or so it has run successfully. In the last 12 days it has failed 9
times, curiously running successfully at the weekend and on one other
occasion.

The job has 4 steps and always fails on the 4th step which runs a DTS
package which contains 2 OLAP tasks, a full proccess of all shared
dimensions and a full process of all cubes, with workflow.

When it fails the message generated by SQL AGENT is always the same
type of message as follows;

************************************************** **************************
Executed as user: ISSQL1\SQLAdmin. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSOlapProcess.Task_1 DTSRun
OnError: DTSStep_DTSOlapProcess.Task_1, Error = -2147221389
(80040073) Error string: File error [during the Open operation]
'C:\Program Files\Microsoft Analysis Services\Data\BIS_MI_LIVE\Payment
Term'S.dim The process cannot access the file because it is being used
by another process. ' Error source: Payment Term Help
file: Help context: 1000440 Error Detail Records:
Error: 0 (0); Provider Error: 0 (0) Error string: File error
[during the Open operation] 'C:\Program Files\Microsoft Analysis
Services\Data\BIS_MI_LIVE\Payment Term'S.dim The process cannot access
the file because it is being used by another process. ' Error
source: Payment Term Help file: Help context: 1000440
DTSRun OnFinish: DTSStep_DTSOlapProcess.Task_1 DTSRun: Package
execution complete. Process Exit Code 1. The step failed.
************************************************** **************************

I cannot find any reference to this either on the net or in Books
online. We did install several security patches in the days before it
started to fail, but I don't know what they were.

Anyone seen this before?

Hi,

I think job is failing because a resource used by job is locked by
some process or application. You can use SQL Server Profiler to watch
activity going on server when job runs.

Also, note that job must be designed with administrator user of sql
server other wise job may not execute. but this is not happening in
your case as you specified job was running earlier.

I feel SQL Server Profiler will solve assit in digging cause of
failure.

Thanks & Regards,
Amit

james.aitchison@budgetinsurance.com (James Aitchison) wrote in message news:<d272ef98.0309180329.2e70e049@posting.google. com>...
> I have a job which runs on one of our servers overnight. For 3 months
> or so it has run successfully. In the last 12 days it has failed 9
> times, curiously running successfully at the weekend and on one other
> occasion.
>
> The job has 4 steps and always fails on the 4th step which runs a DTS
> package which contains 2 OLAP tasks, a full proccess of all shared
> dimensions and a full process of all cubes, with workflow.
>
> When it fails the message generated by SQL AGENT is always the same
> type of message as follows;
>
> ************************************************** **************************
> Executed as user: ISSQL1\SQLAdmin. DTSRun: Loading... DTSRun:
> Executing... DTSRun OnStart: DTSStep_DTSOlapProcess.Task_1 DTSRun
> OnError: DTSStep_DTSOlapProcess.Task_1, Error = -2147221389
> (80040073) Error string: File error [during the Open operation]
> 'C:\Program Files\Microsoft Analysis Services\Data\BIS_MI_LIVE\Payment
> Term'S.dim The process cannot access the file because it is being used
> by another process. ' Error source: Payment Term Help
> file: Help context: 1000440 Error Detail Records:
> Error: 0 (0); Provider Error: 0 (0) Error string: File error
> [during the Open operation] 'C:\Program Files\Microsoft Analysis
> Services\Data\BIS_MI_LIVE\Payment Term'S.dim The process cannot access
> the file because it is being used by another process. ' Error
> source: Payment Term Help file: Help context: 1000440
> DTSRun OnFinish: DTSStep_DTSOlapProcess.Task_1 DTSRun: Package
> execution complete. Process Exit Code 1. The step failed.
> ************************************************** **************************
>
> I cannot find any reference to this either on the net or in Books
> online. We did install several security patches in the days before it
> started to fail, but I don't know what they were.
>
> Anyone seen this before?