由于“活动事务”，数据库“tempdb”的事务日志已满。

Question

我正在使用 Microsoft SQL Server 2014，并尝试创建一个临时表时遇到了一些问题。我之前已经运行过这段代码，没有任何问题，但今天尝试运行查询时，出现了两个错误，“数据库中已经存在名为'#AllClasses'的对象”或“由于'ACTIVE_TRANSACTION'，数据库'tempdb'的事务日志已满。”我在下面粘贴了代码的一部分：

    CREATE TABLE #AllClasses(studentId uniqueidentifier, ClassName nvarchar(100), SchoolName nvarchar(100), AcademicYearId uniqueidentifier, UserGroupId uniqueidentifier, SchoolId uniqueidentifier, ClassId uniqueidentifier,  UserGroupOrganizationStatusId tinyint);

    CREATE UNIQUE INDEX #I_AllClasses ON #AllClasses (StudentId, UserGroupId);

    INSERT #AllClasses(studentId, ClassName, SchoolName, AcademicYearId, UserGroupId, SchoolId, sc.ClassId, u.UserGroupOrganizationStatusId)

    SELECT sc.studentId, c.ClassName, u.UserGroupOrganizationName, c.AcademicYearId, c.UserGroupId, c.SchoolId, sc.ClassId, u.UserGroupOrganizationStatusId
    FROM StudentClassCrossReference sc
    INNER JOIN class c ON sc.ClassId = c.classId
    INNER JOIN School s ON s.SchoolId = c.SchoolId
    INNER JOIN dbo.UserGroupOrganization u ON u.UserGroupOrganizationId = s.UserGroupOrganizationId
    GROUP BY sc.studentId, c.classname, u.UserGroupOrganizationName, u.UserGroupOrganizationId, c.AcademicYearId, c.UserGroupId, c.SchoolId, sc.ClassId, u.UserGroupOrganizationStatusId
    HAVING u.UserGroupOrganizationStatusId = 0

在尝试删除表时，我遇到了一个新的错误，提示“无法删除表 '#All Classes'，因为它不存在或者您没有权限。”

        DROP Table #LS25Student, #AllClasses, #LS25PageSession, #LS25PsByClass

当我尝试使用IF语句删除表时，我收到了错误消息：“由于'ACTIVE_TRANSACTION'，'tempdb'的事务日志已满。”

    IF OBJECT_ID('tempdb.dbo.#AllClasses', 'U') IS NOT NULL 
      DROP TABLE #AllClasses; 

    IF OBJECT_ID('tempdb.dbo.#LS25Student', 'U') IS NOT NULL 
      DROP TABLE #LS25Student;

    IF OBJECT_ID('tempdb.dbo.#LS25PageSession', 'U') IS NOT NULL 
      DROP TABLE #LS25PageSession;

    IF OBJECT_ID('tempdb.dbo.#LS25PsByClass', 'U') IS NOT NULL 
      DROP TABLE #LS25PsByClass;

我能够运行其他查询，但是这个特定查询无法运行。有什么建议可以解决此问题将不胜感激。

- Lenny

---

可能是数据库事务日志已满的重复问题。 - Giulio Caccin

@GiulioCaccin 谢谢您的建议，但我在事务日志上运行了一个查询，发现用于tempdb的日志空间仅为31％。您提到的其他查询也无法帮助我理解为什么我创建的临时对象无法被删除或创建。除非您的建议中有我所忽略的内容。 - Lenny

Answer 1

你可以通过简单的语句 SELECT * FROM tempdb..sysobjects WHERE name LIKE '%AllClasses%' 来搜索所有的临时对象。
要解决它，只需运行一次：

BEGIN TRANSACTION
    DROP TABLE #AllClasses
COMMIT TRANSACTION

如果您仍然无法删除它，请使用SELECT * FROM sys.dm_exec_sessions检查僵尸会话，并使用KILL session_id终止它。

- Giulio Caccin

---

如何识别空数据（nulls、zeros）的僵尸会话？ - Bronek

这是一个有趣的问题，适合发布在 https://dba.stackexchange.com 上！ - Giulio Caccin

Answer 2

这个错误发生在执行提交之前对数据库进行繁重查询的情况下。

例如，如果您尝试在数百万条记录的表上进行一些分析计算的子查询，然后再对其进行更新，那么由于必要的计算，tempdb 将会增加到最大尺寸（因为必要的计算），直到达到最大尺寸并出现该错误。

可能的解决方案包括:

• 在可行的情况下将操作分为段，例如基于某些键对一组行进行更新
• 增加 tempdb 的尺寸（显然如果有足够的磁盘空间）

- Alessandro C

Answer 3

我曾经遇到过同样的问题，当我增加了'tempdb'数据库的日志大小（初始大小）时，问题就解决了。（您可以使用SSMS并选择“tempdb”数据库的属性）

- Bronek

Answer 4

关于表删除问题，我曾经遇到过这种情况：当嵌套的过程调用有一个与调用过程中的临时表同名的临时表时，会出现此问题。
我偶尔也会看到孤立的spids，其中一个临时表处于僵尸状态，并且在检查objectid时不匹配。如果是前者的问题，只需重命名临时表即可。
我还会检查活动spids，并查看是否有挂起的事务，这也可能是事务日志问题的原因，然后杀死它们。使用视图sys.dm_exec_sessions来查看正在运行的内容。

- Xedni

Answer 5

对我来说，从数据库断开连接（在对象资源管理器中右键单击 - 断开连接），然后重新连接解决了问题。

- Ivan Yurchenko