I am trying to perform a cleanup operation on a table using DELETE
and I receive the following error:
Could not allocate space for object 'dbo.SORT temporary run storage: 140767697436672' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Before running the DELETE
I have more than 11G of free disk space. When the error is issued, I have next to nothing on that partition. Context information comes below:
1) Problematic query:
declare @deleteDate DATETIME2 = DATEADD(month, -3, GETDATE())
delete from art.ArticleConcept where ArticleId IN (select ArticleId from art.Article where PublishDate < @deleteDate)
2) Cardinality for involved tables
declare @deleteDate DATETIME2 = DATEADD(month, -3, GETDATE())
select count(1) from art.Article -- 137181
select count(1) from art.Article where PublishDate < @deleteDate -- 111450
select count(1) from art.ArticleConcept where ArticleId IN (select ArticleId from art.Article where PublishDate < @deleteDate) -- 12153045
exec sp_spaceused 'art.ArticleConcept'
-- name rows reserved data index_size unused
-- ArticleConcept 14624589 1702000 KB 616488 KB 1084272 KB 1240 KB
3) Indexes
-- index_name index_description index_keys
-- IDX_ArticleConcept_ArticleId_Incl_LexemId_Freq nonclustered located on PRIMARY ArticleId
CREATE NONCLUSTERED INDEX [IDX_ArticleConcept_ArticleId_Incl_LexemId_Freq] ON [art].[ArticleConcept]
(
[ArticleId] ASC
)
INCLUDE ( [LexemId],
[Freq]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
4) Server
Select @@version
-- Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
-- Feb 20 2014 20:04:26
-- Copyright (c) Microsoft Corporation
-- Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
5) Execution plan (estimated)
I understand that I am performing a large DELETE, but I cannot understand why it requires so much space to do it: the whole ArticleConcept
table has less than 2GB (reserved space), yet to remove records from it requires more than 11GB.
Question: Why does my DELETE command require huge amount of temporary run storage?
I have removed all secondary indexes and I could perform the DELETE
. However, why so much more space is needed to perform the DELETE
when having them, looks strange to me.
I am trying to delete 12,153,045 out of 14,624,589 records (quite many). I have not monitored the transaction log, but once I received an error related to it:
The transaction log for database ... is full due to 'ACTIVE_TRANSACTION'