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.

Possible duplicate of [The transaction log for the database is full](https://stackoverflow.com/questions/17674973/the-transaction-log-for-the-database-is-full) — Giulio Caccin, Jul 24 '17 at 21:16
@GiulioCaccin Thank you for the suggestion, but I ran a query on the transaction logs and the log space used for tempdb is only at 31%. The other inquiry you referred me to also doesn't help me to understand why the temp objects I create refuse to be dropped or created. Unless there's something that I am missing from your suggestion. — Lenny, Jul 25 '17 at 00:09

Giulio Caccin · Accepted Answer · 2017-07-25T06:31:25.957

11

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.

edited Jul 25 '17 at 06:31

answered Jul 25 '17 at 05:29

Giulio Caccin

2,962
6
36
57

How to recognize zombie session? with empty data (nulls, zeros)? – Bronek Dec 03 '19 at 08:29
1

that's an interesting question for https://dba.stackexchange.com ! – Giulio Caccin Dec 03 '19 at 15:38

score 5 · Answer 2 · answered Mar 14 '20 at 13:34

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.

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)

score 1 · Answer 3 · edited Jul 25 '17 at 08:06

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_sessions to see what's running.

score 1 · Answer 4 · answered Dec 03 '19 at 09:51

1

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)

answered Dec 03 '19 at 09:51

Bronek

10,722
2
45
46

score -3 · Answer 5 · answered Aug 20 '18 at 17:38

-3

For me disconnecting from the db (right click on it in Object Explorer - Disconnect) and reconnecting again fixed the issue.

answered Aug 20 '18 at 17:38

Ivan Yurchenko

3,762
1
21
35

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

5 Answers5