[求助]数据文件收缩不能释放空间

[jdhost]

闭门造车，孤陋寡闻，今天手上这个问题实在解决不了，厚颜来求助各位大虾，求指点。

如下：
数据库：SQL SERVER 2005

问题描述：手上这个系统的数据库，是5年前建立的，设计不合理、维护不规范等等就先不用提了，现在有迫在眉睫的问题是，数据文件太大，无法收缩。

只有一个数据文件primary，日志为简单模式。文件大小：1.48T（你没看错，是T不是G），所在盘1.98T，剩余空间80G

该数据库一直没有做历史数据的常规清理、表索引定期重建等工作。所以到我手上的时候，就是这么大了。前些时候硬盘空间不足报警，我把几个历史大表数据统统删除，尤其其中一个巨大的，占空间900G的数据表，直接迁移至另外的服务器，drop原表后重建。

本拟经过数据库收缩，可以数据文件的剩余空间释放出来给系统，但是执行了一次，成功，但数据文件只小了2G；我重建了所有数据表索引，执行数据文件收缩，成功，但数据文件没变小。又执行一次，还是如此。

有没有高手能给些指点？万分感谢，急！

[汤姆克鲁斯]

试试shrinkfile
处于使用状态吗？

[jdhost]

使用状态，是用的shrinkfile

[汤姆克鲁斯]

执行下列操作之一以验证文件是否有足够的可用空间可供删除：


SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

[jdhost]

这个结果怎么分析？
XXX 1026122
XXX_log -420467

[Quote=引用 3 楼 的回复:]
执行下列操作之一以验证文件是否有足够的可用空间可供删除：


SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
[/Quote]

[汤姆克鲁斯]

[Quote=引用 4 楼 的回复:]
这个结果怎么分析？
XXX 1026122
XXX_log -420467

引用 3 楼 的回复:
执行下列操作之一以验证文件是否有足够的可用空间可供删除：


SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.……
[/Quote]
看这个应该有很大的收缩空间啊
收缩不下去可能这个统计信息不准确，用sp_spaceused更新一下

EXEC sp_spaceused @updateusage='true'

然后在运行一下看看

[jdhost]

这个应该是准确的，70%剩余空间，就是释放不出来。

[發糞塗牆]

DBCC shrinkfile是作用在【区】一级的动作，如果你的空间是在一个区里面，那么是无效的。如果重建聚集索引或者对没有的聚集索引的表创建后收缩还是没效果的话（实际上这步是重组数据，使其尽量集中在区里面，从而释放出空的区用于DBCC shrinkfile）。可以尝试（记得做好备份）以下方法：方法来源于《SQLServer2008企业级平台管理》
1、找到特别大的几个表。把数据导入到别的表，然后truncate，而不是delete，然后把数据插回来，然后创建聚集索引，这样是为了处理原表的碎片。
2、利用DBCC EXTENTINFO命令打开数据文件里面的所有区的分配信息。然后计算每个对象理论上区的数目和实际的数据，如果实际数目远大于理论的数目，证明碎片过多。就要考虑重建对象。参考代码：

CREATE TABLE extentinfo
 (
 [FILE_ID] SMALLINT,
 page_id INT,
 pg_alloc INT,
 ext_size INT,
 obj_id INT ,
 index_id INT,
 partition_number INT ,
 partition_id BIGINT,
 iam_chain_type VARCHAR(50),
 pfs_bytes VARBINARY(10)
 )
 go
 
 CREATE PROC import_extentinfo AS 
 DBCC extentinfo('AdventureWorks')
 GO
 INSERT INTO extentinfo
 EXEC import_extentinfo
 GO
 SELECT [file_id],obj_id,index_id,PARTITION_id,ext_size,'actual extent count'=COUNT(*),
 'actual page count'=SUM(pg_alloc),'possible extent count'=CEILING(SUM(pg_alloc)*1.0/ext_size),
 'possible extents /actual extents'=(CEILING(SUM(pg_alloc)*1.00/ext_size)*100.00)/COUNT(*)
 FROM extentinfo
 GROUP BY [file_id],obj_id,index_id,PARTITION_id,ext_size
 HAVING COUNT(*)-CEILING(SUM(pg_alloc)*1.0/ext_size)>0
 ORDER BY PARTITION_id,obj_id,index_id,[file_id]

[KevinLiu]

有试过指定收缩文件的大小吗？如果没有的话可以试试看

如果不可以的话可以按照楼上的
1、找到特别大的几个表。把数据导入到别的表，然后truncate，而不是delete，然后把数据插回来，然后创建聚集索引，这样是为了处理原表的碎片，之后再进行收缩。

[ttpsan550]

[Quote=引用 7 楼 的回复:]

DBCC shrinkfile是作用在【区】一级的动作，如果你的空间是在一个区里面，那么是无效的。如果重建聚集索引或者对没有的聚集索引的表创建后收缩还是没效果的话（实际上这步是重组数据，使其尽量集中在区里面，从而释放出空的区用于DBCC shrinkfile）。可以尝试（记得做好备份）以下方法：方法来源于《SQLServer2008企业级平台管理》
1、找到特别大的几个表。把数据导入到别的表，……
[/Quote]

学习个

[jdhost]

非常感谢，我明天试一下你的方法！
[Quote=引用 7 楼 的回复:]
DBCC shrinkfile是作用在【区】一级的动作，如果你的空间是在一个区里面，那么是无效的。如果重建聚集索引或者对没有的聚集索引的表创建后收缩还是没效果的话（实际上这步是重组数据，使其尽量集中在区里面，从而释放出空的区用于DBCC shrinkfile）。可以尝试（记得做好备份）以下方法：方法来源于《SQLServer2008企业级平台管理》
1、找到特别大的几个表。把数据导入到别的表，然……
[/Quote]

[jdhost]

感谢DBA_Huangzj和汤姆克鲁斯两位，终于定位到问题所在，是有几个表没有建聚集索引，所以foreach进行索引重建时，这几个表实际上没动弹，散乱地霸占了空间不放。