tikkani.praveen (8/13/2009)
Now after doing this activity, if i'll shrink the database i'll get 200 GB of free space. I tried shrinking files using Sql server management studio, but space was not released.
Please give a moment on the advice provided by Gail. If you can add some disk space, u'd be able have enough disk space. Read out dbcc shrinkfile/dbcc shrinkdatabase from BOL if you dont have a choice to shrink the data files.
GilaMonster (8/14/2009)
Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
I believe the OP indicated that he'd moved 200GB of tables out of the DB, into a separate DB.
If so, that would be a valid reason for doing a shrink. Those tables would grow in the other DB, not this one. While he needs to leave sufficient room for growth, he could recover quite a bit of valuable disk space by reclaiming the space he just freed up.
So we don't have all the details, but:
If the tables were moved to a new database and only data that is seldom updated is in the original database then a shrink might be acceptable.
To tikkani.praveen please note that what has not been said to you is that since you removed a great deal of data from your database is that shrinking the database may not bee needed at all. This is because SQL server keeps track of the areas within the file where the old data used to be. As more data is added to the database SQL server will reuse that old space and not have to grow the file. There are was to check for available space within your database. Now if you need the disk space for something else other than this database then that is another issue. Shrinking the database then is needed.
There are lots of bad things that can happen with shrinking and we want you to protect yourself. Unfortunately this is something that take on an almost "religious" fervor by people who will tell you "don't ever do that" without telling you why. Check the Books On Line (BOL) documentation for the command that have been suggested. Then look at what is is that you need to do. Shrinking a database of the size that you state will take quite some time. Nothing else should touch the database while you are shrinking it. Always make sure you have very good backups before doing anything like this.
ATBCharles Kincaid
In SSMS
-Right click / properties on the database
-In files section check the initial size and set it to a lower value
-Click Ok
Now the shrink file/database will work
jjimenez-1040959 - Tuesday, January 16, 2018 5:10 AM
I always appreciate an answer even if it's a bit late. Just so you know, your responding to a post that's over 9 years old.
--Jeff Moden
Change is inevitable... Change for the better is not.
Ah... and to add to that, shrinking a file or a database causes a type of fragmentation that can actually cripple the database. If you shrink a file or a database, you MUST rebuild indexes after the shrink is complete.
--Jeff Moden
Change is inevitable... Change for the better is not.
Jeff Moden - Tuesday, January 16, 2018 7:32 AM
I know, but I find the post today with the same problem.
And was a little confused to see everybody telling "no you should not shrink database! Its bad ! gnagnagna " but without answering to the real question.
I think that it good for the comunity to share when a solution is found.
Bye
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply