记一次SQL Server数据库表体积缩减操作
郝伟 2022/02/23

• 1 简介
• 2 数据库表行数统计
• 3 操作思路
  • 3.1 删除 CURVE 表
  • 3.2 删除 RESULT 表
• 4 文件收缩
  • 4.1 收缩文件
  • 4.2 收集数据库
• 5 删除日志文件
• 6 附：参考SQL查询

1 简介

本文针对给定目标数据库的主要数据表进行删除，将数据库体重从原来的200GB+，缩小为10GB。

2 数据库表行数统计

首先，在拿到这个数据库以后，希望对数据库的数据情况有初步了解。因此使用以下SQL语句查看数据库中各表的记录数最多的最20张表：

SELECT TOP(10)
  schema_name(systable.schema_id) as TableSchema, systable.name as TableName, sysids.rows as NumberOfRows
FROM 
  sys.tables as systable, sysindexes as sysids 
WHERE 
  systable.object_id = sysids.id and sysids.indid <=1 and sysids.rows > 10  
ORDER BY NumberOfRows DESC

返回结果如下

TableSchema	TableName	NumberOfRows
dbo	RESULT	50,525,591
dbo	CURVE	32,477,206
dbo	RESULT_GUID	4,613,348
dbo	USER_INFO	4,868
dbo	TIGHTENING_UNIT_CHANGE	3,235
dbo	CONTROLLER_CHANGE	2,357
dbo	TOOL	278
dbo	USER_INFO_DEF	225
dbo	USER_INFO_TXT	185
dbo	STEP	124

如表所示，数据量最大的是RESULT表和CURVE表，简单查看可以发现 RESULT.RES_ID 和 CURVE.CRV_ID为 为主键，同时在 CURVE 表关联了RESULT表，使用 CURVE.CRV_ResultID 与 RESULT.RES_ID 关联。 从业务层面看，RESULT 表中应该是结果，而有些结果会有曲线（CURVE）。 进一步验证，发现确实存在 RESULT.RES_ID 和 CURVE.CRV_ID 的约束。

3 操作思路

既然这两个是主表，而且都有主键，数据都是增量添加，所以只需要根据主键删除即可。由于约束的存在，所以先删除 CURVE 中的数据再删除RESULT中的数据，具体执行结果如下：

3.1 删除 CURVE 表

DELETE CURVE WHERE CRV_ResultID <= 48500000

删除2000万条数据用时约4个小时。

3.2 删除 RESULT 表

DELETE RESULT WHERE RES_ID <= 48500000

执行用了约6个小时。

4 文件收缩

执行完以后，数据确实是变化了，可以使用 COUNT() 进行验证。但是查看数据库文件信息，发现执行后数据文件体积反而变大了：

cvinet.mdf 323 GB (347,240,136,704 字节)
cvinet_log.ldf 471 GB (506,274,971,648 字节)

其中，数据文件 cvinet.mdf 没有变化, 而日志文件 cvinet_log.ldf 由原来的几十GB增长到471GB，增加了约400GB。

4.1 收缩文件

在执行了 任务 -> 收缩 -> 文件 后，发现体积仍然是一样没有变化。

# 收缩文件后
-- cvinet.mdf 323 GB (347,240,136,704 字节)
-- cvinet_log.ldf 471 GB (506,274,971,648 字节)

4.2 收集数据库

再执行 任务 -> 收缩 -> 数据库 后，发现体积终于大幅缩小了。

# 收缩数据库后
-- cvinet.mdf 7.8 GB (8,019,334,592 字节)
-- cvinet_log.ldf 471 GB (506,274,971,648 字节)

5 删除日志文件

首先，关闭所有连接和查询窗口。然后选择 任务 -> 分离，执行成功后即可删除 cvinet_log.ldf 文件。然后再选择附加数据库，将cvinet.mdf添加进来，即可完成整个数据库收缩工作。
删除后统计数据如下：

TableSchema	TableName	NumberOfRows
dbo	RESULT_GUID	4,613,348
dbo	RESULT	1,525,591
dbo	CURVE	477,206
dbo	USER_INFO	4,868
dbo	TIGHTENING_UNIT_CHANGE	3,235
dbo	CONTROLLER_CHANGE	2,357
dbo	TOOL	278
dbo	USER_INFO_DEF	225
dbo	USER_INFO_TXT	185
dbo	STEP	124

6 附：参考SQL查询

Select TOP 1 RES_DateTime from RESULT Order by RES_ID  desc
-- 2020-11-14 03:08:09.000 to 2021-08-27 09:25:53.000
Select TOP 10 RES_ID, RES_DateTime from RESULT Where  RES_ID > 48500000 Order BY RES_ID   
SELECT COUNT(RES_ID) FROM RESULT WHERE RES_ID > 48500000
DELETE RESULT WHERE RES_ID <= 48500000

SELECT COUNT(RES_ID) FROM RESULT

-- DELETE CURVE WHERE CRV_ResultID <= 48500000
-- 收缩文件没有用
-- 收缩数据库前
-- cvinet.mdf 323 GB (347,240,136,704 字节)
-- cvinet_log.ldf 471 GB (506,274,971,648 字节)

-- 收缩数据库后
-- cvinet.mdf 323 GB (347,240,136,704 字节)
-- cvinet_log.ldf 471 GB (506,274,971,648 字节)

SELECT TOP(10)
  schema_name(systable.schema_id) as TableSchema, systable.name as TableName, sysids.rows as NumberOfRows
FROM 
  sys.tables as systable, sysindexes as sysids 
WHERE 
  systable.object_id = sysids.id and sysids.indid <=1 and sysids.rows > 10  
ORDER BY NumberOfRows DESC