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.
There are basically two things to consider:
- You cause fragmentation when shrinking the database
- 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.