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
-- 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)
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'