SQL 由于“ACTIVE_TRANSACTION”，数据库“tempdb”的事务日志已满

The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'

提问by Lenny

I'm using Microsoft SQL Server 2014 and have run into some issues trying to create a temporary table. I've run this code once before with no issue, but when I tried to run the query today, I received one of two errors "There is already an object named '#AllClasses' in the database" or "The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'." I have pasted a part of the code below:

我正在使用 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

When I try to drop the table, I get a new error which reads, "Cannot drop the table '#All Classes' because it does not exist or you do not have permission."

当我尝试删除表时，出现一个新错误，内容为“无法删除表 '#All Classes'，因为它不存在或您没有权限。”

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

And when I tried using an IF statement to drop the table, I received the error "The transaction log for 'tempdb' is full due to 'ACTIVE_TRANSACTION'."

当我尝试使用 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;

I am able to run other queries without issue. Any suggestions to fix this particular query would be greatly appreciated.

我可以毫无问题地运行其他查询。任何修复此特定查询的建议将不胜感激。

采纳答案by Giulio Caccin

You can search all the temp objects with a simple SELECT * FROM tempdb..sysobjects WHERE name LIKE '%AllClasses%'
To fix it just run once:

您可以使用简单的方法搜索所有临时对象，SELECT * FROM tempdb..sysobjects WHERE name LIKE '%AllClasses%'
只需运行一次即可修复它：

BEGIN TRANSACTION
    DROP TABLE #AllClasses
COMMIT TRANSACTION

If you still cant delete it just check for zombie sessions with SELECT * FROM sys.dm_exec_sessionsand kill it with KILL session_id.

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

回答by Xedni

With regard to the table drop issue, I have seem this happen when a nested procedure call has a temp table with the same name as a temp table in the calling procedure.
I have also very occasionally seen orphaned spids where a temp table is sort of in a zombie state, and doesn't match when you check the objectid. If it's the former issue just rename the temp table.
I would also check active spids and see if there are any hung transactions which could also be the cause of the transaction log issues, then kill them. The view is sys.dm_exec_sessionsto see what's running.

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

回答by Ivan Yurchenko

For me disconnectingfrom the db (right click on it in Object Explorer - Disconnect) and reconnectingagain fixed the issue.

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

回答by Alessandro C

This error happens when you are doing onerous queries on the DB before a commit performs.

当您在执行提交之前对数据库进行繁重的查询时，会发生此错误。

For example, if you try to make a subquery with some analytic calculations over a table with millions of records and then you make an update on it, the tempdb grows in dimensions (because of the necessary calculations) until it reaches the maximum dimensions, giving that error.

例如，如果您尝试对具有数百万条记录的表进行一些分析计算进行子查询，然后对其进行更新，则 tempdb 的维数会增长（由于必要的计算），直到达到最大维数，给出那个错误。

The possible solutions are:

可能的解决方案是：

• Reduce the operations in segments wherever possible, for example making an update on a reduced set of rows (based on some keys)
• Increase the dimensions of the tempdb (obviously if there's enough disk space)

• 尽可能减少段中的操作，例如对减少的行集进行更新（基于某些键）
• 增加 tempdb 的维度（显然如果有足够的磁盘空间）

回答by Bronek

I had the same problem and it went away when I had increased Log Size of 'tempdb' database (Initial Size). (you can use SSMS and choose Properties of 'tempdb' database)

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