SQL Job事务日志有问题 [英] SQL Job having issues with transaction log

问题描述

我有一个仅执行存储过程的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:

当前请求有一个活动用户 交易，但发生错误 导致交易被 归类为不可承诺 交易.该请求无法提交 交易或回滚到 保存点；它只能要求完整 交易回滚.这 请求无法执行任何写入 操作，直到它回滚 交易.该请求只能 执行读取操作，直到滚动 支持交易.之后 交易已回滚， 请求可以执行读取和 编写操作并可以开始新的操作 交易.

The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction. The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The request cannot perform any write operations until it rolls back the transaction. The request can only perform read operations until it rolls back the transaction. After the transaction has been rolled back, the request can perform both read and write operations and can begin a new transaction.

您尝试执行此操作仅表明您的异常处理存在代码问题(换句话说，您的过程有错误).我最近在博客中发布了使用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事务日志有问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！