Database Size Increased after DELETE query

Question

I have a database with large varbinary(MAX) duplicate records in one of my tables in SQL Server 2019. We needed to remove the duplicates so I ran a delete query to remove the duplicates. Instead of the database size remaining the same before the delete statement, it rather increased after the completion of the query. I am about to apply DBCC SHRINKFILE(N'myDatabase', 0), but I am really curious to know why the database size went up. Attached is my Delete query.

DELETE from Document WHERE DocumentId in 
(
    SELECT t.DocumentId
    FROM (
        SELECT s.*,
          ROW_NUMBER() OVER (PARTITION BY s.DocumentName, s.SubmitDateTime, s.DocumentType ORDER BY s.DocumentName, s.SubmitDateTime, s.DocumentType) AS [RowCount]
        FROM Document s
        LEFT OUTER JOIN Staging b ON s.DocumentId = b.DocumentId
        LEFT OUTER JOIN RejectTable r ON  s.DocumentId = r.DocID
        WHERE b.DocumentId IS NULL
          AND r.DocID IS NULL
          AND s.SubmitDateTime IS NOT NULL
          AND s.InsertDateTime IS NOT NULL
          AND s.DocumentName IS NOT NULL
          AND s.DocumentContent IS NOT NULL
          AND s.Description ='EmployeesDocument'
    ) t
    WHERE t.[RowCount] > 1
)

score 7 · Accepted Answer · answered Jun 29 '23 at 15:32

When you say the database file size increased, what are you looking at?

I would expect that the log file size increased, but should be mostly empty either at the end of the transaction (if in SIMPLE recovery mode) or empty after the most recent Log backup (if in BULK/FULL recovery mode).

In either case, however, the database does not release file space back to the OS unless you run a shrink file. Please only do this as a one-time operation to shrink back to a reasonable size, taking care to defragment indexes after it completes.

If dbo.Document is a heap then you have an additional consideration. Heaps don't typically release space from deleted records until the table is rebuilt. See this excellent answer for further assistance on this - How to reclaim the storage space used by a heap?

Database Size Increased after DELETE query

1 Answers1