SQL Server Agent job fails with "The step was cancelled (stopped) as the result of a stop job request"

Question

Hello!

Since 3 days ago, an SQL Server 2005 Agent job that's used to retrieve data from an odbc data source (Microsoft XAL) fails with the following error:

"Executed as user: NT AUTHORITY\SYSTEM. The step was cancelled (stopped) as the result of a stop job request.".

As far as I can tell, we didn't change anything in the configuration of the job in the last few days. Any ideas what settings I should check to find the cause of the problem? Or had somebody had similar problems?

Thanks in advance!

Answer 1

I've found the solution. We had a stored procedure that was waiting via WAITFOR DELAY if another stored procedure was still running. And somehow, our database got inconsistent, so the stored procedure thought the other procedure was running all the time and waited and waited and waited...

At some point in the night, when the server got restarted because of a backup job, the agent worte this "The step was cancelled (stopped) as the result of a stop job request." into the job log.

Maybe this will help someone in the future who has the same problem.