从SQL Server删除行增加了数据库的大小

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

我用c#编写了一些代码，从数据库中删除记录。它是事务中包含的简单的SQL delete代码。

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

我已经删除了数据库中4个表中的400万行。当我通过Windows Explorer检查这两个DBs的数据库备份大小时，它的大小急剧增加了大约三倍

Before deletion: 7GB
之前删除:7 gb
After deletion: 28GB
后删除:28 gb

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.

我确认MDF和LDF文件的大小都增加了。我还注意到，一些不受delete影响的表的索引大小增加了。

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
database_size:61050.69 MB
unallocated space: 9592.60 MB
未分配的空间:9592.60 MB
reserved: 303512 KB
保留:303512 KB
data: 207040 KB
数据:207040 KB
index_size: 53024 KB
index_size:53024 KB
unused: 43448 KB
未使用:43448 KB

Before deletion:

在删除之前:

database_size: 10067.88 MB
database_size:10067.88 MB
unallocated space: 16.69 MB
未分配的空间:16.69 MB
reserved: 7290176 KB
保留:7290176 KB
data: 2937656 KB
数据:2937656 KB
index_size: 4324848 KB
index_size:4324848 KB
unused: 27672 KB
未使用:27672 KB

3 个解决方案

#1

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.

这两项措施是强制性的。

#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

请参见下面的链接https://msdn.microsoft.com/en-us/library/ms190488(v=sql.90).aspx

#3

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.

其中_indexName是聚集索引的名称。

参见https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql

#1