I often get emails from my blog readers asking various types of clarifications about my own blog. Here is one of the interesting questions from a reader about message Cannot shrink file in the database to xxxx pages as it only contains yyyy pages which he encountered after following my below blog.
Actually, he modified the script and put the hardcode value in shrink file command to 1 GB size and he was getting an information message.
It is easy to reproduce the error message.
USE [master] GO IF DB_ID('DB_Shrink_Test') IS NOT NULL BEGIN ALTER DATABASE [DB_Shrink_Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE [DB_Shrink_Test] END GO CREATE DATABASE [DB_Shrink_Test] GO USE [master] GO ALTER DATABASE [DB_Shrink_Test] MODIFY FILE ( NAME = N'DB_Shrink_Test_log', SIZE = 2GB ) GO -- shrinking first time to minimum possible size using my script (I have added my database name) DECLARE @ScriptToExecute VARCHAR(MAX); SET @ScriptToExecute = ''; SELECT @ScriptToExecute = @ScriptToExecute + 'USE ['+ d.name +']; CHECKPOINT; DBCC SHRINKFILE ('+f.name+');' FROM sys.master_files f INNER JOIN sys.databases d ON d.database_id = f.database_id WHERE f.type = 1 AND d.database_id > 4 AND d.name = 'DB_Shrink_Test' --SELECT @ScriptToExecute ScriptToExecute EXEC (@ScriptToExecute) -- shrinking again with 1 GB size USE [DB_Shrink_Test] GO DBCC SHRINKFILE(DB_Shrink_Test_log,1024) GO -- cleanup /* USE [master] GO ALTER DATABASE [DB_Shrink_Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO USE [master] GO DROP DATABASE [DB_Shrink_Test] GO */
Here is the output.
Cannot shrink file ‘2’ in database ‘DB_Shrink_Test’ to 131072 pages as it only contains 1024 pages.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
—— ———– ———– ———– ———– ————–
8 2 1024 1024 1024 1024
(1 row affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
If you know the basics of SQL Server shrinking, the message should be easy to understand but for those, who are not very well versed with SQL Server database architecture, it would be an interesting read.
Read message again. Let’s covert the pages to size and read it again. Each page is 8 KB in size.
Cannot shrink file ‘2’ in database ‘DB_Shrink_Test’ to 1024 MB as it only contains 8 MB.
So, the message is essentially saying that you are trying to shrink a file and given the number which is more than the current size.
In my case, the database was initially created with 8 MB size (taken from the model database) that’s why my script was able to shrink to maximum possible size.
Reference: Pinal Dave (https://blog.sqlauthority.com)