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

Question

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:

    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."

        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 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.

Answer 1

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

BEGIN TRANSACTION
    DROP TABLE #AllClasses
COMMIT TRANSACTION

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