Question about DBCC Shrinkfile

I've got a SQL Server database that was restored from production (SQL Server 2000) to a development instance (SQL Server 2005). After the restore I deleted about 90% of the user data, by copying 10% to a temp table, truncating, and re-inserting the data.

I tried to shrink the files, but the biggest file won't shrink. Running sp_helpfile, I see:

name fileid filename filegroup size maxsize growth usage
MyDB_Data 1 M:\Data\MyDB_Data.MDF PRIMARY 190504832 KB Unlimited 1024000 KB data only

So the file is about 181 GB, but I know that it's mostly empty space. Running sp_spaceused gives me 142531.77 MB unallocated space, which I know is mostly in the MDF file. When I run DBCC Shrinkfile(MyDB_Data, 170000), the command runs successfully but there is no change in the file size. Does anyone have any idea why the file won't shrink? I've seen this in log files before but never in data files.

Results from shrinkfile:

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
5 1 23813104 2560000 5686656 5686656

Dylan Peters

shrinkdb only shrinks the database down at most to its original created size. That means if the db file can only shrink down to the MinimumSize.|||

oj wrote:

shrinkdb only shrinks the database down at most to its original created size. That means if the db file can only shrink down to the MinimumSize.

In this case the minimum size is 2560000 pages or 10000 MB, just 10 GB. The current file size is 23813104 pages or 93019 MB, and the pages used comes out to 22214 MB. So I should be able to get it down to about 22 GB in theory. The BOL for shrinkfile says it can be used to shrink the file to less than its original created size, but I'm not going below 10 GB anyway.

Dylan Peters

|||No. You cannot shrink it less than the original created size.

http://msdn2.microsoft.com/en-us/library/ms190488.aspx

"The database cannot be made smaller than the minimum size of the database. The

minimum size is the size specified when the database is originally created, or

the last size explicitly set by using a file size changing operation such as

DBCC SHIRNKFILE or ALTER DATABASE. For example, if a database is originally

created with a size of 10 MB in size and grows to 100 MB, the smallest the

database can be reduced to is 10 MB, even if all the data in the database has

been deleted."

But that is not your current problem. My guess is that you have removed a bunch of blob but you can't reclaim the space. See the following kb for detail.

http://support.microsoft.com/default.aspx/kb/324432