March 29, 2012 at 9:25 am
I have a database that has multiple files and separated by filegroups. In the primary group the file has over 70% available free space when I go to Tasks, Shrink, File in SSMS. The other FGs that contain files are also over 50% available free space.
The question is at this point what is the most efficient way: to release unused space or to reorg pages before releasing unused space or shrink each file to reflect approx. 30 to 40% or some other solution? Any advice would be appreciated.
March 29, 2012 at 10:18 am
May I ask why are you trying to shrink the files? Are you running low on disk space, or are you just trying to keep things tidy? If the latter and you do not have disk space issues then I recommend you leave things as they are, and forego any shrinking. Here's why: Stop Shrinking Your Database Files. Seriously. Now.[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 29, 2012 at 12:55 pm
I was concerned about low disk space. But it seems as if I need to watch to see if it stablize. Along with the database the log files grows and I want to set up a regularly schedule job to shrink the log files. Is this a good idea?
March 29, 2012 at 1:20 pm
Kasnut (3/29/2012)
I was concerned about low disk space. But it seems as if I need to watch to see if it stablize. Along with the database the log files grows and I want to set up a regularly schedule job to shrink the log files. Is this a good idea?
No. It is a terrible idea. Please read the article from my previous post, as well as all articles it links to.
If your log files are growing very large something (a bad query most likely) is making them do that. If you shrink the log, it will simply grow again the next time that "something" happens again, only compounding the issue.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 29, 2012 at 1:35 pm
April 20, 2012 at 2:11 am
Shrinking is a bad idea as you'll probably end up with high index fragmentation thus affecting performance. Rebuilding the indexes to improve the performance will increase the size of database.
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
April 20, 2012 at 3:55 am
A once-off shrink is fine, as long as it is once-off (run once, not regularly) and you shrink to a size that still leaves enough free space in the files for index rebuilds or data growth.
70% free space is a little extreme. If you aren't likely to reuse that in 6 or so months from regular data growth, shrink the data files once (to maybe 25% free space) and then rebuild indexes.
The log file I wouldn't shrink until you know how much log space is needed for regular database activity.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply