February 10, 2010 at 8:07 am
We have a database that we want to shrink. I understand that checking the box to Reorganize files before releasing unused space might take more time to run but could improve performance afterwards.
I am unclear as to what percentage I should enter for the the Maximum free space in files after shrinking.
Can someone advise?
Currently, this is what is showing:
Currently allocated space: 15846.38 MB
Available Free Space: 6.98 MB (0%)
Thank you
Roger
February 10, 2010 at 8:23 am
Roger Abram (2/10/2010)
We have a database that we want to shrink. I understand that checking the box to Reorganize files before releasing unused space might take more time to run but could improve performance afterwards.
It will likely decrease performance due to the fragmentation of the indexes that the shrink will cause.
Currently allocated space: 15846.38 MB
Available Free Space: 6.98 MB (0%)
7 MB free in a 15 GB database? Why on earth do you want to shrink? That's almost no free space in the file. I'd personally advise you to grow that file slightly, not shrink it.
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/
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
February 10, 2010 at 9:06 am
I second Gail's thoughts, shrinking causes fragmentation in the first place, and there is no significant amount of free space in your database to think about shrinking.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
September 17, 2014 at 7:22 am
I have a Database that is currently 642 GB is size with 317 GB free (49%).
I do not know how it got so large but what would be the best course of action?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 17, 2014 at 9:58 am
Post moved to 2008 DBA.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply