Post reply

Shrinking a live DB

  • December 10, 2007 at 5:40 am

    #122750

    Hi All,

    I'm running SQL 2005 and have got a 450GB database that needs to lose some weight. I've restored a backup of the DB and done a shrink on it and it goes down to about 250GB which is great but it takes about 2 days to do it. As we can't have our main DB out of action for 2 days, my question is: can I shrink the live DB while people are still using it or will it all go horribly wrong? :crazy:

    Cheers,

    Stu

  • December 10, 2007 at 6:09 am

    #758548

    You can, but you will have a performance impact. How much depends on your hardware and the activity.

    Try shrinking in smaller chunks. Sometimes that works out faster overall. Even if it doesn't you could perhaps shrink little by little over several weekends.

    Be sure to rebuild all your indexes afterwards, as the shrink will badly scramble them.

    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

  • December 10, 2007 at 6:17 am

    #758553

    - maybe you may also want to migrate the most critical objects to a new filegroup, so they will not be influenced that much at shrink time.

    - shrinking is done object by object, so (dead)locking may occur frequently. As long as your spid isn't choosen as deadlockvictim, you're ok.

    Plan this action !

    Notify db-users or responcibles so they know what will be going on.

    - maybe also disable some rarely used indexes.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • December 10, 2007 at 7:38 am

    #758600

    Thanks for your help.

    Regards,

    Stu

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

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