SS2000 - Intermitted Job failures

[magicmushroom]

Hi - I have a job that contains a step that runs a CmdExec. The CmdExec is running an exe that imports data from a csv into the db. The job regularly fails with : 'The step did not generate any output. Process Exit Code -1073741819. The step failed.'

I have a run a trace and I can a lot of inserts hitting the db, followed by the commit. Up to now everything looks OK. However, after the commit there are several minutes where, according to the trace file, there appears to be no activity on the db.

The job then updates the job log with the above message, even tho' the job step appears to have been successfull...

Any advices gratefully accepted!

 

[mrdenny]

Sounds like the exe that is being run needs to be debugged.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[magicmushroom]

Hi denny - it's not the exe. Exe is doing exactly what it should and issue only arises if run via job agent. Problem is that once trans are committed 'nothing happens' for a while and then job agent logs an error. The exe has it's own log file. Log file says it ran successfully and finished at the same time that the commit completed.

M

 

[SQLSister]

The step did not generate any output"

Since you are doing an import what output was it supposed to produce? What action should happen onthe completion of the import? That's where I would look first. We've had DTS jobs fail before because the success message couldn't be sent as the Exchange server was down.

"NOTHING is more important in a database than integrity." ESquared

 

[mrdenny]

What's the return value that the exe is returning? Is the SQL Agent job configured to look for the correct value?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[magicmushroom]

...although I managed to spell it wrong, the failure is intermittent... when it is successfull the agent log states :

Executed as user: 'x'. The step did not generate any output. Process Exit Code 0. The step succeeded.

On the trace file the log update happens immediately after the commit is made. When it fails there is a delay, sometimes of up to 10 mins, before the failure is written to the log. The agent appears to 'hang'. The import job log states that import was successfull and everything suggests that the import exe shutdown straight after the commit, so at this moment in time I'm assuming it is a problem with the the agent, not the exe.

thanks - M

 

[magicmushroom]

...final attempt to get to the bottom of this one... ran trace, set sql agent to trace messages, created output file for CmdExec... result :

i) CmdExec log file empty
ii) No mention of 'failure' in sql server agent logs
iii) trace file shows transaction commited and logout, then sits there with no apparent db activity for 10 minutes when agent logs in writes 'failure' to the logs...

Give up? M

 

[SQLSister]

are you getting a deadlock?


"NOTHING is more important in a database than integrity." ESquared

 

[magicmushroom]

...deadlocks weren't included in the trace, but the delay is occuring after the commit is made. There is no activity on the db...