1

I have read conflicting articles about shrinking a databases. Basically, our database grew 100% overnight from 150 gigs to 300 gigs b/c I believe it was with staging tables I put in. However, all the extra spaces is unused (150 gigs worth of unused data). Should I shrink my db at this point? What problems could this do if I shrink the database? Also, any other suggestions would be appreciated.

  • 2
    Specifying the database you're using, whether these staging tables are going to fill up again in the future, and exactly what you mean by "shrink" (i.e. the command you'd want to run) would be helpful. – Justin Cave May 24 '15 at 13:15
  • Hi, I am planning on using 'DBCC SHRINKDATABASE'. I dropped this staging tables and I am going to put them offline in a different database for performance reasons. However, after dropping the tables I still did not reclaim the space. Our db has normally been around the 120 150 gig the last year and last night went up to 300 gigs b/c of what I did (possibly). I need to get that unused space back b/c the sys admin is complaining. Also, would rebuilding indexes give me my space back? Thanks! – Daniel Paduck May 24 '15 at 13:20
  • So I would ask to clear things did data file grew or the log file?. – Shanky May 24 '15 at 17:09
  • Can you show me result of below query just to see is everything configured correctly and we will see if we have any other option than shrinking db. sp_helpdb 'your database name' – Nikhil May 24 '15 at 19:23
3

There are basically two things to consider:

  1. You cause fragmentation when shrinking the database
  2. If the database grew yesterday, there's a good chance it will grow again tomorrow.

To address 1, you need to defrag indexes once you've shrunk the database. The database will grow when you do this, although probably not back to the original size.

To address 2, ask yourself why the database grew. If you know it was a one time thing, then you're probably OK to shrink it. If you know it's a recurring thing, then it's best not to shrink. If you're not sure, do it once manually, but keep a close eye on things and make sure you don't make a habit of it.

Regularly shrinking is a waste of time, will cause you performance issues and one day you'll run out of space without realising it because the database grows overnight but you didn't realise it was at 99% capacity because it was scheduled to shrink everyday before you got into the office.

Keep in mind that the shrink should be carefully planned maintenance on live servers, it'll conflict with things like backups and might put significant load on the server.

  • Thanks. Yes, I believe that it is a one time thing. I was basically trying to create massive staging tables but will offload it to a different database. Besides shrinking are there any other techniques to remove unused space? Thanks! – Daniel Paduck May 24 '15 at 17:05
  • No I don't think there are other options; shrinking sounds like the appropriate solution here. – Matt May 27 '15 at 9:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.