Regarding database file shrinking

  • Hi

    MY db is around 150 GB and i have 11 data files with more different capacities. My DB is in simple recovery mode.

    I just need to confirm if i go to shrink file option in the enterprise manger and should i truncate the free space from the file , because shriking the database is not freeing up any space.

    Can anyone please suggest.

  • Shrinking a database is generally a bad idea unless there is a compelling reason to do so. Are you running out of disk space? Why are you looking to shrink the database?

  • Yes, as above said, we can try to avoid the shrink option, unless you really needed to free up some space to OS. Have a look at the below link you'll get an idea.

    http://www.sqlservercentral.com/Forums/Topic489349-357-1.aspx#bm489445

  • Select Database right click-> Tasks -> Shrink database -> click OK

  • what are you shrinking data files or log files

    SQL server will not shrink past an active portion of the file, if that happens to be near the end of the physical file then it will never shrink. Again why do you want to shrink the file(s)? Is it because they are consuming more space than you planned for?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Don't shrink your database. It causes massive index fragmentation and the DB will quite likely just grow again, possibly causing file-level fragmentation.

    See - shrinking databases[/url]

    Why do you want to shrink?

    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
  • this shrinking malarky has been done to death all over the forum:D

    good job cos i learnt something new 😉

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry Whittle (7/14/2008)


    this shrinking malarky has been done to death all over the forum:D

    I know. That's why I wrote the blog post in the first place, so I didn't have to keep on explaining why shrinking is bad.

    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
  • GilaMonster (7/14/2008)


    I know. That's why I wrote the blog post in the first place, so I didn't have to keep on explaining why shrinking is bad.

    😉

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 9 posts - 1 through 8 (of 8 total)

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