Deleting rows from SQL Server increased the database size

Question

I have written some code in C# that deletes records from a database. It's your straightforward SQL delete code enclosed in a transaction.

using (SqlTransaction trans = conn.BeginTransaction()) {
    //some delete code
    trans.Commit();
}

I have deleted about 4 million rows across 4 tables in my database. When I checked the database backup size of both DBs via Windows Explorer, the size has dramatically increased about thrice

Before deletion: 7GB
After deletion: 28GB

I confirmed that both the MDF and the LDF files increased in size. I also noticed that some tables that are not affected by the delete have their index size increased.

After deletion:

Before deletion:

My goal is to delete records to reduce the database size but it increased instead. Can you tell me why the size is increasing?

EDIT:

After deletion:

database_size: 61050.69 MB
unallocated space: 9592.60 MB
reserved: 303512 KB
data: 207040 KB
index_size: 53024 KB
unused: 43448 KB

Before deletion:

database_size: 10067.88 MB
unallocated space: 16.69 MB
reserved: 7290176 KB
data: 2937656 KB
index_size: 4324848 KB
unused: 27672 KB

This is more appropriate for dba.se, since the reason for this (and the answer) has nothing to do with C#. Any `DELETE` that deletes lots of rows in one transaction will demonstrate this issue. — Jeroen Mostert, May 04 '16 at 10:05
I would expect the LDF to definitely increase, and the MDF to at least stay the same size but probably increase. So give us some numbers. And use `sp_spaceused` to work out how much space is used and how much space is reserved. Normally a delete will free up space in a MDF but won't decrease the actual file (since it will probably just need to grow it again) — Nick.Mc, May 04 '16 at 10:10
Possible duplicate of [Database size bigger after delete from table](http://stackoverflow.com/questions/10865201/database-size-bigger-after-delete-from-table) — pmcilreavy, May 04 '16 at 10:14
I thought sp_spaceused split out log and data files but clearly it doesn't. This link has a handy query to find used and free space in your data and log files. http://dba.stackexchange.com/questions/59406/free-space-of-mdf-and-ldf-does-not-match-with-database-free-space What I suspect is that you'll have a lot of free unlreleased space in your data file. Are there other operations ocurring in this database? — Nick.Mc, May 04 '16 at 11:22
@Nick.McDermaid not that I know of. it's really just a straightforward delete — jmc, May 04 '16 at 11:37

score 2 · Answer 1 · answered May 04 '16 at 10:13

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.

score 2 · Answer 2 · answered May 04 '16 at 10:19

2

Deleting rows in a database will not decrease the actual database file.

You need to compact the database after row deletion.

Please see below link https://msdn.microsoft.com/en-us/library/ms190488(v=sql.90).aspx

answered May 04 '16 at 10:19

Mohit

17
7

score 0 · Answer 3 · answered Apr 27 '17 at 21:07

If there are clustered indices for the tables where the rows were deleted, the deletion may either leave the size of the tables and indices unchanged or even increase them. Often, the primary key for table is based on a clustered index.

In this situation, you should rebuild the indices using the command

alter index _indexName on _tableName rebuild

where _indexName is the name of the clustered index.

see also https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql

Deleting rows from SQL Server increased the database size

3 Answers3

Linked