SQL 作业存在事务日志问题

Question

我有一个仅执行存储过程的 SQL 作业。 每天早上当作业尝试运行时，我都会收到以下错误：

当前事务无法提交，并且无法支持写入日志文件的操作。

当我继续尝试重新启动作业时，它总是给我同样的错误。 但是，如果我只是执行存储过程而不使用作业，它就可以正常工作。

这是非常非常棘手的部分。 如果我只是运行存储过程，取消它，然后运行作业，则作业工作得很好。

有没有人遇到过这个相当独特的问题或者有任何想法可能导致它？

Answer 1

此错误表明您正在尝试在注定失败的事务期间执行记录的操作。 仅当您忽略 XACT_STATE 值为-1：

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

事实上，您尝试执行此操作仅表明您的异常处理存在代码问题（换句话说，您的过程有错误）。 我最近在博客中介绍了 使用 BEGIN TRY 的过程模板/BEGIN CATCH 您可以使用它作为修复程序的起点。 Erland Sommarskog 有一篇关于 Transact-SQL 错误处理的著名文章，但这并没有太深入地涵盖 BEGIN TRY/BEGIN CATCH 。

通过适当的错误处理，您可以找出发生的原始错误并导致 CATCH 块首先被执行。 既然您提到手动运行该过程不会导致任何问题，那么问题可能是 SQL 代理作业和手动执行之间的上下文差异。 如果没有任何数据，我无法诊断问题，但我猜测最可能的原因是安全上下文的差异（即代理登录缺少您自己的登录所拥有的某些权限）。

Answer 2

我在不同的条件下收到此错误，并提出了一个短复制路径（软件缺陷缺少 IF XACT_STATE() != 1 ROLLBACK）：

-- prepare SP

IF OBJECT_ID(N'ShortReplicationPath',N'P') IS NOT NULL     DROP PROCEDURE dbo.ShortReplicationPath
GO

CREATE PROCEDURE dbo.ShortReplicationPath
AS 

BEGIN  
    BEGIN TRY   

        insert #TempTabDateTime (ValidFrom) Values ('date')

    END TRY

    BEGIN CATCH

        PRINT ERROR_NUMBER();        PRINT ERROR_MESSAGE();

    INSERT INTO     #TempTabVarChar
    (       Text    )
    VALUES  (       'abcdefg'   )

    END CATCH

END 

GO

-- Execute test:

IF OBJECT_ID ('TEMPDB..#TempTabDateTime') IS NOT NULL drop table #TempTabDateTime

create table #TempTabDateTime (ValidFrom DATETIME)

IF OBJECT_ID ('TEMPDB..#TempTabVarChar') IS NOT NULL drop table #TempTabVarChar

create table #TempTabVarChar (Text VarChar(MAX))

BEGIN TRANSACTION

    EXEC dbo.ShortReplicationPath
ROLLBACK

GO