Database File Management ->> Shrink Data File

今天在开发环境遇到了一个问题，我们发现服务器上的硬盘空间满了，查看了下发现这个盘存放的数据库文件应该是来源一个并非很大的库才对。检查之后发现这个数据库下的某个数据文件占了盘符下70%的空间，而大部分数据都来自某张表。但是即便我删除了该表里面所有的数据，这个数据文件并不会把空间都还给操作系统啊。那就面临一个如何把收缩文件大小的问题了。我最先想到的还是SQL Server下的DBCC SHRINKFILE命令，按道理来讲连续执行两条DBCC SHRINKFILE命令应该是可以达到收缩数据库文件大小的目的的。

第一条命令把文件尾部的页面尽量往文件内部靠前的空闲页面填充，这种适用于数据文件而不适用于日志文件；第二条命令把文件尾部的空白页面截断并还给操作系统。这样看好像便可以达到目的了。但是，这两条命令跑起来太慢了。首先这个文件是100GB大小，使用页面所占空间其实只有十分之一也就是10GB。第二条命令我花了快3个小时都没跑完。而且重要的是，第二条命令会造成blocking的现象。在SHRINKFILE的过程中出现某位同事无法访问某张表存储在该文件中的数据。

USE [YourDB]
GO

DBCC SHRINKFILE (28, NOTRUNCATE);
DBCC SHRINKFILE (28, TRUNCATEONLY);

 

于是乎我想到了另外一个办法

1）检查下同一文件组下面的其他数据文件的剩余是否大于要收缩的文件的实际已用空间大小(space used)。

2）如果是，DBCC SHRINKFILE(<FILE_ID>,EMPTYFILE)。过程可能有点慢。可以用脚本来监控数据库的空闲空间的变化（脚本1）

3）如果否，ALTER DATABASE ADD FILE加入一个文件，同不同磁盘不管，总之整个文件组下除了要收缩的文件外的其他文件的剩余可用空间总大小要大于收缩文件的已用空间大小。然后再运行DBCC SHRINKFILE(<FILE_ID>,EMPTYFILE)。

4）整个SHRINKFILE的过程最好是SET SINGLE_USER。

5）这个时候你就可以用ALTER DATABASE REMOVE FILE把文件移除出文件组。

6）这个时候如果前面你加入了文件，而你又希望文件回到原来的盘下，就SET DATABASE OFFLINE然后ALTER DATABASE MODIFY FILE修改路径，然后把文件拷贝到和你修改的路径一致的地方，然后SET DATABASE ONLINE; 如果前面不需要添加文件，而你又希望文件回到原来的盘下，直接ALTER DATABASE ADD FILE到文件组就行。

 

脚本1：

use [MyDB]; 
GO

select DB_NAME() AS DbName, 
CONVERT(varchar(20),DatabasePropertyEx('MyDB','Status')) ,  
CONVERT(varchar(20),DatabasePropertyEx('MyDB','Recovery')),  
sum(size)/128.0 AS File_Size_MB, 
sum(CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT))/128.0 as Space_Used_MB, 
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,'SpaceUsed') AS INT))/128.0 AS Free_Space_MB  
from sys.database_files  where type=0 
and file_id = 28
group by type

 

有一个需要注意的是：

DBCC SHRINKFILE是单线程的操作