• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1264
  • Last Modified:

Help shrinking database (.ndf nd .mdf) files

I have a database that has one .mdf files and 7 .ndf files, all in the Primary filegroup. I'm trying to shrink the database files to recover disk space. I've tried it from Ent. Manager and through SQL, but the files do not shrink. Is there anything that I'm missing. Please let me know. Thank you
1 Solution
Hello itagent007,

I remember reading somewhere that a database cannot be shrunk to a size that is less than their original size (specified when the database was created). This might be your problem.
Try backing up your database, create a new database with a smaller initial size, then restoring into the new database.


itagent007Author Commented:
So back up the database, create a new database with minimum size and then let it grow as it restores? Won't that restore the entire size of the .ndf file instead of just growing the new database fies to what it originally was?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think Dabas has the following article in mind, speaking about the log files:

I am not sure if that is what applies here...
so, what version?
how much space is actually used by tables?
how big are the individual files actually?

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Guy Hengel [angelIII / a3]Billing EngineerCommented:
the backup/restore method will not work, as the restore will reset the file sizes as they have been backed up...
itagent007Author Commented:
The individual .ndf files range from 100 GBs to 200 GBs, but only a small portion of them are being utilized. I'm wondering if it's better to move all of the data off of them into a single .mdf file. Does anyone know how to do that? The database is SQL 2000.
The shrink task in Enterprise Manager should have a "empty the file (data will migrate to other files in the group)" option - at least SQL 2003 does...

Make sure to click on the "Files" button in the "Shrink Files" section of the Shrink Database dialog box.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now