Transaction log for temp db is full. How not to use a log?

Question

I have a rather heavy DELETE operation that tries to delete rows in a temp table which do not have a match in another database table.

DELETE FROM ##SendRecipients
WHERE Id NOT IN (SELECT RecipientId FROM MyDB.dbo.Results)

After about 10 minutes, I get the follwoing error message:

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

My attempts at adding a new log file for tempdb was to no avail. And now the database files together with the .ldf files of the log (spread across three drives), have clogged the server space.

Is there a way to tell tempdb not to use a log, at least only for this operation? It seems like one cannot modify much in tempdb.

Does moving the temp tables inside the databases help as an alternative?

If you're populating a temporary table, why not ensure the correct data goes in first my adding a NOT EXISTS clause to the INSERT statement? — Thom A
– Thom A ♦, Commented Mar 2, 2019 at 18:54
Short answer: NO. You can't not use log in SQLServer (and most of the RDBMS-es too). As for prolonged answer: it's a case when specific version and edition of SQLServer is crucial — Alex Yu
– Alex Yu, Commented Mar 2, 2019 at 18:54
I would look for: a) DBCC OPENTRAN - look for open transactions, maybe there are culprits, b) check if it's not a case, c) "moving the temp tables inside the databases" - certainly could solve the problem but another problems could arise — Alex Yu
– Alex Yu, Commented Mar 2, 2019 at 19:00

David Browne - Microsoft · Accepted Answer · 2019-03-02 19:10:31Z

3

First, you probably don't want to use a global temp table. Their utility is very limited.

To reduce the logging, you could do this:

select *
into #SendRecipients2
from #SendRecipients
WHERE Id IN (SELECT RecipientId FROM MyDB.dbo.Results)

Which should be minimally-logged, or if you aren't in an explicit transaction

while 1=1
begin
   DELETE top (10000) FROM #SendRecipients
   WHERE Id NOT IN (SELECT RecipientId FROM MyDB.dbo.Results)
   if @@rowcount = 0 break;
end

which will allow the transaction log space to be reused for each statement.

Another option is to use a table variable instead of a temp table. Modifications to table variables are never logged.

edited Mar 2, 2019 at 19:10

answered Mar 2, 2019 at 19:00

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

disasterkid Over a year ago

I will try both your solutions and come back to you. Does using global temp tables cause trouble?

David Browne - Microsoft Over a year ago

Yes. If you run two instances of your stored procedure, or any other stored procedure that uses the same global temp table names, they will interfere with each other.

Collectives™ on Stack Overflow

Transaction log for temp db is full. How not to use a log?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related