Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2006
    Posts
    3

    Unanswered: To Shrink or not to Shrink ??

    Newbie question about reclaiming some disk space on our SQL server.

    We currently have a db that has the following stats:

    space allocated: 34734 MB
    space free: 843.66

    of this the current size of .mdf is 33877 MB and the space used is 33873 MB

    the drive that holds this database is running out of space, what is the best method for me to use to reclaim some disk space?

    I have read books online and am a little bit confused as to whether or not using shrinkfile on the .mdf will free up space to the OS? Do I need to do anything else before or after such as reindex? Also what is the overhead as we only have about 10% free on the drive.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you shrink you need to recognise this is a bandaid - you need more disk. This is assuming that you have not done some major one off delete or something else that means that your mdf is unusually and disporportionately bloated.

    A reindex will actually cause disk problems - DBCC DBREINDEX is one of the causes of db "bloat". Which is one of several reasons why you should manage your disks to suit the database rather than the other way round.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2006
    Posts
    3
    How much space can I expect to gain by shrinking? Is it only the 2% that is currently reported as free in the MDF?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Broadly yes. Try running DBCC UPDATEUSAGE to make sure the figures you are seeing are accurate.

    To reiterate - your problem is the disk not the mdf file.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2006
    Posts
    3
    I do realize that this is a temporary fix for a larger problem, I just wanted to make sure that 1, the shrink will not cause any more problems as far as space is concerned and 2, that it is worth doing as far as getting space back to the OS. So from what I gather i do not need to do any other maintenance either before or after the shrink?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •