SQL server job failed.

Question

Post reply

SQL server job failed.

BoobyB

SSCertifiable

Points: 5470
More actions
January 20, 2011 at 12:13 pm

#157400

Hello Guys,
we have a production server A and Mirror server B for DR.
every thing on the server a is exactly the same as server b.
mirroring is set up at server b
I have this job that pulls the data from a table in a database and puts it in a table in a different database on server a.
the name of the job lets say is jobA
it runs every 15 min and copies the data
the job is moved to mirror server every night
the job on server a is runing fine
but the same job on server b is failing since last night.
in the job view history I not giving me any details just error saying the job failed on step 1.
pls help.
Thanks in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

Check the second node of the job history that could have a detailed message.

If not check the SQL Server Logs or Windows System logs to identify any issues that could result in the job failure.

Also please post any detailed error messages.

Thank You,

Best Regards,

SQLBuddy

BoobyB SSCertifiable Points: 5470 More actions · Answer 2

the first node said simply " The Job was invoked by User sa. The last step to run was step 1 (step1).

and the second node says " domain\sqlagentaccount. d DTSER_FAILURE (1). Started: 1:41:20 PM Finished: 1:42:03 PM Elapsed: 42.656 seconds. The package execution failed. The step failed.

I am looking at the sql server error log ... haven't seen any thing yet .

Thanks in advance

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

Is this a job created by a Maintenance Plan or is this a job running SSIS package.

Thank You,

Best Regards,

SQLBuddy

BoobyB SSCertifiable Points: 5470 More actions · Answer 4

SSIS package.

the package was created on production and job ran on it, still runing there , from production it moves using a sql transfer job task in ssis package and been runing on the mirror for a while too , just stopped last night.

i am not so sure if when a ssis package job gets transfer does it transfers the package on the mirror server too or just the job??

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

It copies only the job, not the package.

Actually what does the job do on Server B i.e Mirror Server ?

Was the Job on the Mirror able to run successfully in the past or is this the first time you were trying to run it on the Mirror ?

Also do you have any custom logging Enabled for the package ?

Thank You,

Best Regards,

SQLBuddy

BoobyB SSCertifiable Points: 5470 More actions · Answer 6

The job has been running since it was creaed until last night. funny thing is the job is still running perfectly on the production server.

Sry i dont know what u mean by custom logging . can u please explain

Thanks in advance

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 7

Custom logging means you specify a log file that will logs the events that happen in the SSIS package during its execution. If you already have one then it becomes really easy to find the root cause.

Pls check the following link for more details

http://www.sqlbiinfo.com/2010/02/ssis-logging.html

Thank You,

Best Regards,

SQLBuddy

BoobyB SSCertifiable Points: 5470 More actions · Answer 8

just wondering.. if the job is running on principal server , migrating to mirror server , than i suppose it means the ssis package is working.

does ssis package work on one server and when u migrate the sql agent job to a different server and when it doesn't work there , still could be the ssis package issue or some thing else.

as far as i know package is working that why the job worked on the principal server ??

any other take on this i am all ears?

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 9

Is the package stored on Sql Server or as file system package ?

If stored on SQL Server , the Job should run fine on both servers.

If stored as File system Package, the Job success depends on how you configured the path for the package within the package, whether you used UNC Path or Local path. If used UNC path, then it will succeed on both servers.

As the job running fine previously , it should have been stored on SQL Server.

the first node said simply " The Job was invoked by User sa. The last step to run was step 1 (step1).
and the second node says " domain\sqlagentaccount. d DTSER_FAILURE (1). Started: 1:41:20 PM Finished: 1:42:03 PM Elapsed: 42.656 seconds. The package execution failed. The step failed.

With this limited information its really hard to find the root cause.

Is the job still failing on the Mirror ? If yes, then pls think and rethink what could be causing the issue.

Thank You,

Best Regards,

SQLBuddy