DBCC SHRINKDATABASE xxxx was skipped because the file does not have enough free

假设你创建一个数据库时,指定其初始化大小(SIZE )非常大。例如,如下测试案例所示

USE [master]

GO

 

CREATE DATABASE [TEST] ON  PRIMARY 

( NAME = N'TEST_Data', FILENAME = N'D:\SQL_DATA\TEST_Data.mdf' , SIZE = 11527027KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )

 LOG ON 

( NAME = N'TEST_log', FILENAME = N'D:\SQL_LOG\TEST_log.ldf' , SIZE = 40350KB , MAXSIZE = 2048GB , FILEGROWTH = 32768KB )

GO

此时我们检查数据库的空间使用情况如下所示:

USE TEST;

GO

EXEC SP_SPACEUSED;

DBCC SHRINKDATABASE xxxx was skipped because the file does not have enough free

然后你使用DBCC SHRINKDATABASE去收缩数据库时,就会遇到“DBCC SHRINKDATABASE: xxx was skipped because the file does not have enough free space to reclaim”这样的错误。

USE TEST;

 

GO

 

DBCC SHRINKDATABASE('TEST', 5);

DBCC SHRINKDATABASE: File ID 1 of database ID 8 was skipped because the file does not have enough free space to reclaim.

DBCC SHRINKDATABASE: File ID 2 of database ID 8 was skipped because the file does not have enough free space to reclaim.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC SHRINKDATABASE xxxx was skipped because the file does not have enough free

是不是有点纳闷,明明数据库有大量空闲空间,但是提示“DBCC SHRINKDATABASE: File ID 1 of database ID 8 was skipped because the file does not have enough free space to reclaim" 没有足够剩余空间回收。我们先用DBCC SHRINKFILE将数据库的数据文件收缩一下,如下所示

USE TEST;

GO

DBCC SHRINKFILE('TEST_Data', 20);

DBCC SHRINKDATABASE xxxx was skipped because the file does not have enough free

DBCC SHRINKDATABASE xxxx was skipped because the file does not have enough free

如上所示,DBCC SHRINKFILE可以收缩数据库文件,但是DBCC SHRINKDATABASE为什么不行呢?

DBCC SHRINKDATABASE ( database_name | database_id | 0 [ , target_percent ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ) [ WITH NO_INFOMSGS ]

这个是因为DBCC SHRINKDATABASE是数据文件/日志对象的百分比来收缩数据库,并且这个收缩后的值不能小于数据文件/日志文件的最小大小(minimum file size). 但是DBCC SHRINKFILE收缩时,可以小于数据文件/日志文件的minimum file size。官方文档介绍如下:

收缩后的数据库不能小于数据库的最小大小。 最小大小是在数据库最初创建时指定的大小,或是使用文件大小更改操作(如 DBCC SHRINKFILE 或 ALTER DATABASE)显式设置的最后大小。 例如,如果数据库最初创建时的大小为 10 MB,后来增长到 100 MB,则该数据库最小只能收缩到 10 MB,即使已经删除数据库的所有数据也是如此。

所以上面DBCC SHRINKDATABASE命令不能收缩数据库,因为上图的Mimimum Size和 CurrentSize大小相同。而使用DBCC SHRINKFILE可以收缩数据库文件.

参考资料:

https://msdn.microsoft.com/zh-cn/library/ms190488.aspx

https://blogs.msdn.microsoft.com/psssql/2008/03/24/how-it-works-dbcc-shrinkdatabase-shrinkfile-tabular/

上一篇:Android 编辑框插入表情图片


下一篇:一、Redis基本操作——String(原理篇)