Post reply

shrinking multiple datafiles

  • January 4, 2013 at 1:51 pm

    #266732

    I have a database that is 50 GB, 27 GB of used data (there are nore than one data file) and 22 of data free. I want to release the unused data space. Can you please let me know what the best way is to do it? If you suggest shrink datafile, can you please let me know how you shrink multi datafiles?

    Lava

  • January 4, 2013 at 1:56 pm

    #1573947

    DBCC ShrinkFile on each file. Once you've done that, rebuild all indexes. Don't shrink as small as possible, leave some free space in the files

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 4, 2013 at 2:06 pm

    #1573953

    Gail,

    How about if I do: DBCC SHRINKDATABASE (dbname, 10)? Would that better? There are over 20 datafiles in the database.

    Thank you!

    Lava

  • January 4, 2013 at 2:29 pm

    #1573962

    Better, no. Easier, yes.

    Shrinkdatabase gives you no control over the free space left in each file, just in the DB as a whole, plus it shrinks the log file as well (which you may not want to have happen).

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 4, 2013 at 2:32 pm

    #1573964

    Thanks for your input, will do the datafiles than the whole database.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply