sql server - SQL Job having issues with transaction log -

I have a SQL job that only executes a stored procedure. Every morning when the job tries to run, I get the following error:

The current transaction can not be committed and can not support works written in the log file.

When I am trying to resume the job, this gives me the same error, however, if I execute the store process without just using the job then it works fine.

And here's a really hard part. If I run the stored procedure, cancel it, and then run the job, the job works perfectly well.

Has anybody come in this unique problem or what is the reason that can be due to it?

This error indicates that you try to perform a log operation during a wasted transaction Are there. If you are ignoring the value of -1 then it can only be in the BEGIN catch block:

There is an active user transaction in the current request, but an error has occurred, causing the transaction to occur Should be classified as an undocumented transaction, the request can not do transaction or roll back to a savings point; It can only request full rollback of the transaction. This request can not run any write operation until the transaction is withdrawn. This request can only be done by reading, until the transaction takes off. After the transaction is brought back, both can work to read and write the request and start a new transaction.

The fact is that you try to do this only with your exception handling (in other words your process is small). I have recently blogged and you can use it as a starting point to fix your process. Erland is in Sommarskog, but the BEGIN true / cash catch is not included in very depth.

With proper error handling in place, you can then search for the original error that occurs to execute your CATCH block first place. Since you mentioned that there is no problem with running the process manually, the problem is that there is a difference in the context between the SQL agent job and your manual execution. I can not diagnose the problem without any data, but there is a difference in the security context in my estimation of the most probable reasons (i.e. the agent login lacks some of your rights).