Tempdb growing while deleting millions of records

Question

I had to run a delete command which deletes almost 4 million records. While deletion it's noted that tempdb data file is growing as much as the table size and going to fill up the drive.

My questions are:

Since I'm running a single delete command is causing the tempdb growth?
Will the space consumed in tempdb get automatically cleared when the connection is closed?
Is there any way we can avoid this situation?

Which database you are using? Once you make `COMMIT` temp data should be removed. — Digvijay S, Mar 31 '20 at 04:17
One way to avoid is to insert rows that you want to retain in the new table, truncate the current table and reinsert to original table. — Digvijay S, Mar 31 '20 at 04:18
Thank you for all your replies and they are really helpful. I looks I found out the issues. The database was set READ COMMITTED SNAPSHOT ISOLATION On which is causing the tempdb to hold the copy of data which is going to modify. So I split the single commands to multiple with where condition, — Peter, Apr 13 '20 at 07:17

Venkataraman R · Answer 1 · 2020-03-31T05:27:56.427

Answering your questions one by one:

Since I'm running a single delete command is causing the tempdb growth?

Could be. As you are running a huge DML operation, some data from buffer cache is moved to tempdb for carrying out the operation and it is causing the tempdb growth.

Will the space consumed in tempdb get automatically cleared when the connection is closed?

No. the tempdb will not automatically shrink. When the database service is restarted, tempdb is created fresh and will reset to its original size.

Is there any way we can avoid this situation?
- You can go for small batches for UPDATE operation.

DECLARE @rowCount INT 
SET @rowCount = (SELECT COUNT(*) FROM TableName WHERE ColumnName = 'Somevalue')


WHILE @rowCount <> 0
BEGIN
BEGIN TRANSACTION
UPDATE TableName
SET ColumnName = 'Newvalue'
WHERE PrimaryKeyId IN (select top 10000 PrimaryKeyId FROM TableName WHERE ColumnName = 'Somevalue');
COMMIT TRANSACTION;
SET @rowCount = (SELECT COUNT(*) FROM TableName WHERE ColumnName = 'Somevalue');
END

Try to go for SELECT * INTO to another table and then rename the newly created table to current tableName

SELECT col1, updatedCol2
 INTO new_tableName
FROM tablename;

sp_rename tablename, old_tablename
sp_rename new_tablename, tablename

There are more suggestions available in the Stackoverflow post

Also, Microsoft has some guidelines on tempdb performance improvement

score 0 · Answer 2 · answered Mar 31 '20 at 05:30

Since I'm running a single delete command is causing the tempdb growth?

when you delete records then it log to transaction log. so by deleting so many records,transaction log size increases.

So i think you have to clear/reset transaction log (Google it) and at the same time you have reorganise indexes .

Both steps are mandatory.

Will the space consumed in tempdb get automatically cleared when the connection is closed?

NO

Is there any way we can avoid this situation?

Are you deletting part of table, more than 80% ?

Tempdb growing while deleting millions of records

2 Answers2