Help shrinking database (.ndf nd .mdf) files

Posted on 2007-07-21
Last Modified: 2008-02-01
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
Question by:itagent007
    LVL 27

    Expert Comment

    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.


    LVL 1

    Author Comment

    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?
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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?

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    the backup/restore method will not work, as the restore will reset the file sizes as they have been backed up...
    LVL 1

    Author Comment

    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.
    LVL 35

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now