原文: 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;
http://images2015.cnblogs.com/blog/73542/201608/73542-20160816175626312-114426647.png 然後你使用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.
http://images2015.cnblogs.com/blog/73542/201608/73542-20160816175629156-217058843.png是不是有點納悶,明明資料庫有大量空閑空間,但是提示“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);
http://images2015.cnblogs.com/blog/73542/201608/73542-20160816175631093-1814880392.png http://images2015.cnblogs.com/blog/73542/201608/73542-20160816175633343-1783990893.png 如上所示,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/