CODEWITHSUNDEEP
sqlsql-server
Lenny
Lenny

Reputation: 203

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

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.

Upvotes: 17

Views: 140219

Answers (6)

Joshua Carmody
Joshua Carmody

Reputation: 13740

In my case, it turned out the disk that SQL Server was installed on was full and the transaction log couldn't be expanded. I solved this by moving the tempdb files to a different drive. I used the following script found at https://www.brentozar.com/archive/2017/11/move-tempdb-another-drive-folder/

SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],'
    + ' FILENAME = ''Z:\MSSQL\DATA\' + f.name
    + CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END
    + ''');'
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb');

This generates another set of SQL statements that you can copy-and-paste from the results and run.

Upvotes: 1

Alessandro C
Alessandro C

Reputation: 3560

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)

Upvotes: 6

Bronek
Bronek

Reputation: 11245

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)

enter image description here

Upvotes: 4

Ivan Yurchenko
Ivan Yurchenko

Reputation: 3871

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

Upvotes: -3

Xedni
Xedni

Reputation: 4715

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.

Upvotes: 1

Giulio Caccin
Giulio Caccin

Reputation: 3052

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.

Upvotes: 13

Related Questions