问题描述
我有一个仅执行存储过程的SQL作业.每天早上作业尝试运行时，都会出现以下错误:
当前事务无法提交，不能支持写入日志文件的操作.
I have a SQL job that simply executes a stored procedure. Each morning when the job attempts to run, I get the following error:
The current transaction cannot be committed and cannot support operations that write to the log file.
当我继续尝试重新启动作业时，它总是给我同样的错误.但是，如果我只执行存储过程而不使用Job，它就可以正常工作.
When I keep trying to restart the job, it keeps giving me that same error. However, if I just execute the store procedure without using the Job it works fine.
这是真正非常棘手的部分.如果我只是运行存储过程，将其取消，然后运行Job，则该工作可以正常运行.
And here's the really really tricky part. If I just run the stored procedure, cancel it, and then run the Job, the job works perfectly fine.
有没有人遇到这个相当独特的问题，或者有什么想法可能导致这个问题?
Has anyone come across this rather unique problem or have any ideas what may be causing it?
推荐答案
此错误表明您正在失败的事务中尝试执行记录的操作.如果您忽略 XACT_STATE ，则只能在BEGIN CATCH块中发生这种情况a>值为-1:
This error indicates that you are trying to do a logged operation during a doomed transaction. This can only happen in a BEGIN CATCH block if you're ignoring the XACT_STATE value of -1:
您尝试执行此操作仅表明您的异常处理存在代码问题(换句话说，您的过程有错误).我最近在博客中发布了使用BEGIN TRY的过程的模板/BEGIN CATCH ，您可以以此为起点来修复程序. Erland Sommarskog有有关Transact-SQL错误处理的著名文章，但这并不能太深地覆盖BEGIN TRY/BEGIN CATCH.
The fact that your trying to do this merely indicates a code problem with your exception handling (in other words your procedure is buggy). I have recently blogged about a template for procedures that use BEGIN TRY/BEGIN CATCH and you can use that as a starting point to fix your procedure. Erland Sommarskog has a well known article on Transact-SQL error handling, but that does not cover the BEGIN TRY/BEGIN CATCH too deeply.
使用适当的错误处理，您便可以找出原来发生的错误，并导致您的CATCH块首先被执行.由于您提到手动运行该过程不会导致任何问题，因此问题可能出在SQL Agent作业和手动执行之间的上下文差异.我无法在没有任何数据的情况下诊断问题，但是我最可能的原因是安全上下文的差异(即，代理登录缺少您自己的登录所拥有的某些权限).
With proper error handling in place you can then find out the original error that occurs and causes your CATCH block to be executed in the first place. Since you mention that running the procedure manually causes no issues then the problem is likely the differences in context between the SQL Agent job and your manual execution. I cannot diagnose the problem without any data, but my guess about the most likely cause is the difference in the security context (ie. the Agent login is lacking some rights your own login has).
这篇关于SQL Job事务日志有问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！