Page 1 of 5 123 ... LastLast
Results 1 to 15 of 72
  1. #1
    Join Date
    Jul 2007
    Posts
    17

    Unanswered: How to Shrink SQL Server database

    First of all thanks for taking interest in this thread.

    I have database in Microsoft SQL Server and its size is appx. 427 GB. It consists of data from 2006. Everyday I expire data of 2-3 days and then try to shrink database as much as possible.

    But it frees only 2-3 MB after 7-8 hours process. And in that database there some files which do not shrink even I put one of them on shrinking for whole day.

    And as per company policy, I am only allowed to expire data which are 15 months older.

    I am facing very much problem as my daily import and publish activity some times got failed for that reason.

    If anyone knows the solution please tell me the solution.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The simple answer would be don't shrink it. Shrinking db files is really not a good idea especially on a regular basis.

    Moving thread to proper forum.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2007
    Posts
    17
    Then what should I do?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by chintan@saama
    Then what should I do?
    Don't shrink the file

    Are there any pressing needs for why you are shrinking? Is this an active database with ongoing modifications?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2007
    Posts
    17

    How to free the occupied space from the database?

    Yes, I have 2 databases. One is currently in use and occupies 202 GB and the other, older one, which is not in use but have to keep data up to 15 month older, occupies appx. 427 GB.

    I have 9 drives of 100 GB and there is restriction that each drive must have 10% free space. As daily input to new database done, it grows rapidly but older one doesn't frees occupied space even after expiring data older then 15 months.

    Thus, my application which does daily import and publish activity get failed.

    That's why I shrink the database.

    Please tell me the solution for that.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Buy more disks. I am serious - why fit your database to your server not the other way round?
    10% free space is rather low too. You can't even do a proper physical defrag AFAIK.

    Check out the BoL entry for shrinking databases and files. Paul Randal (SQL Server enginge lead at MS) ensured that some of the disadvantages of this approach made it into the manual. If you try googling you will find hundreds of examples of people wnating to do what you want to do and being advised not to do it.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jul 2007
    Posts
    17
    Thanks for serious advice! But this is not my pc or my company's pc but it is my company's client’s server pc.

    And in near future they have no plan to add any drive as there is much total free space. But problem is that on some drive it has 50 GB free space and on some drives only 12 GB.

    I also tried to move datafiles but some files are more than 50 GB so can’t move to other drive.

    So still any serious advice???

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Listen to the Flump. The Flump speaks Wisdom.

    Do.
    Not.
    Shrink.
    Database.

    I wish Microsoft had not even included database shrinking as an option.

    Tell.
    Your.
    Client.
    To.
    Buy.
    More.
    Disk.
    Space.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Mar 2006
    Posts
    53
    I *love* useless advice.

    Listen to Chintan. *He* needs to shrink the database-and nobody will tell him how. Doesn't matter whether it's a good idea or not. He's been told to shrink the database. He has two options-shrink the database or leave his job. Your advice boils down to option 2: leave his job because they're telling him to do something that *you* don't think is a good idea.

    I'm not arguing whether or not shrinking the database is a good idea or not, but I am arguing that ignoring Chintan's problem is a *bad* idea. For him, at least.

    Chintan: If I could help you, I would-but I'm faced with the same problem & the same lack of useful advice.
    Now back on Oracle. The more things change, the more they stay the same.

  10. #10
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by calvin-c
    I *love* useless advice.

    Listen to Chintan. *He* needs to shrink the database-and nobody will tell him how. Doesn't matter whether it's a good idea or not. He's been told to shrink the database. He has two options-shrink the database or leave his job. Your advice boils down to option 2: leave his job because they're telling him to do something that *you* don't think is a good idea.

    I'm not arguing whether or not shrinking the database is a good idea or not, but I am arguing that ignoring Chintan's problem is a *bad* idea. For him, at least.

    Chintan: If I could help you, I would-but I'm faced with the same problem & the same lack of useful advice.
    It's NOT useless advice. It is sound, practical advice.

    The only thing I would add to it would go something like this:

    What you face is not a technical issue. It is a business issue. Business people respond better to technical problems when they are presented in business terms. This usually means presenting an issue and couching it in terms of cost versus benefit.

    I would approach the problem in this fashion:

    Quote Originally Posted by Email
    John [or my manager's name],

    I am technically unable to shrink the database because of limitations in MS SQL Server. There are several ways to deal with this issue:

    1. We can do nothing. Eventually the application will fail because there is insufficient storage. This will cost the business no money, but the downside is that the application will no longer function and users will not have access to the most recent data.

    2. We can add disk space to the existing server. This would involve purchasing XX disks at YY cost per disk for a total of ZZZ dollars. I do/do not have experience installing these disks and so there is/is not additional cost associated with bringing in a technician to do the installation.

    3. We can selectively eliminate more data than what is being purged now. Instead of C years' retention, we can instead choose B years' retention. This will/will not put us in violation of selected audit rules/business requirements. Once more data has been eliminated, it may be possible for me to shrink the database effectively.

    My recommendation would be to implement Option X above.

    I look forward to working through this issue with you.

    Regards,

    Don B. Azalea
    My advice:
    1. Never approach your manager with a business problem without having at least two solutions that you have thought through.

    2. Always include the option to do nothing. It at least rephrases the problem in terms that a manager can comprehend.

    3. Learn to speak in terms of Return on Investment. It will make you a more well-rounded (and hence valuable) person in your operation.

    4. Never talk down to your boss/supervisor. They make decisions that affect your paycheck. Speak to them in terms that they can understand, but never assume that they are ignorant. Try to step out of your world on occasion and see things from their point of view. It can be very enlightening.

    Good luck.

    By the way, I agree with Pootle and Blindman here: shrinking the db is NOT the way to go.

    Regards,

    hmscott


    Edited for clarity.
    Last edited by hmscott; 07-05-07 at 15:47.
    Have you hugged your backup today?

  11. #11
    Join Date
    Dec 2002
    Posts
    1,245
    Pootle Flump...nice sig!!!



    hmscott
    Have you hugged your backup today?

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by calvin-c
    Chintan: If I could help you, I would-but I'm faced with the same problem & the same lack of useful advice.
    Hint: It's not the advice that is useless...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    sometimes, late at night, when the wind is still and the water on the pond is a smooth black mirror, you can hear the Flump calling out, wailing a sad song of how you shouldn't shrink databases.

    who will listen?

  14. #14
    Join Date
    Mar 2006
    Posts
    53
    If the advice doesn't solve the problem, then it's useless-IMO. The problem isn't whether or not to shrink the database-that's already been decided (and not at my level). If I disagree with that decision I can quit or be fired.

    The problem is *how* to shrink the database. Your advice doesn't solve that, so it's useless. JMHO.
    Now back on Oracle. The more things change, the more they stay the same.

  15. #15
    Join Date
    Mar 2006
    Posts
    53
    Should have addressed hmscott's reply, too. That was a nice reply & would be useful in other situations. Doesn't work here as the decision has already been made. Stating that I'm unable to shrink the database would work if I actually were unable to shrink it-but that's not the case. Both my boss & his boss know that the database can be shrunk so that approach simply tells them that *I* can't shrink it-and that's likely to get me fired. (Don't know about where you work but being unable to do something you were hired to do is grounds for dismissal here.)
    Now back on Oracle. The more things change, the more they stay the same.

Posting Permissions

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