Post reply

To Shrink or Not to Shrink.... alternatives?

  • August 4, 2016 at 12:53 pm

    #328821

    Hi there!

    So, I have a DB in production that is 32 gigs. I inherited this database with lots of flaws and have slowly been fixing things over the years. We are only allowed 99 gigs on the drive that is storing my database data files. We have various other db's on this hard drive as well. My drive is running out of space.

    So, I took a look at my 32 gig database today and I noticed that there was one wonky table sitting there with 38 million records in it! Turns out... we don't even use this stupid table. So, I truncated it.

    The DB now has 26 gigs of free space out of the 32 its occupying.

    So, that 1 table was taking up a TON of space. We have issues with DB backup files being too big to move around and I would LOVE to have this free space back.

    I know all about the woes of shrinking the db. I am wondering.... is it ok to do in this one instance? Our DB doesn't have a huge growth rate without these crazy erroneous data tables sitting around. I have custom built procedures that rebuild indexes (not the built in ones) and update statistics on the database. If I shrink the DB and then run my utility to rebuild.... would that cause me a bunch of issues?

    Thanks!

  • August 4, 2016 at 1:04 pm

    #1893927

    If the database is not going to grow back, you could certainly shrink it. Remember to leave enough space for future growth.

    The backups don't include free space, so you shouldn't have any difference there.

    The only benefit is that you could comply with your policy and probably use some of the freed space for something else. Other than that, the free space shouldn't harm you at all.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 4, 2016 at 1:20 pm

    #1893931

    Ok how dumb do I feel that I didn't know the backup wouldn't bring the whole file size with it. So we should be much better off with the backups then.

    I may still shrink it then cause they are also looking at going to the cloud and I need some reasonable growth rate factors and not sure they would want us doing a 32 gig db vs a 7 or 8.

    Thank you!!

  • August 8, 2016 at 11:58 pm

    #1894464

    Hi Amy26

    I suggest you to implement proper backup policy on server, so you don't need to worry about free space. Please also put auto growth on all database and disable auto shrink option

    Thanks

    Saurabh Sinha

    My Blog[/url]

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

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