September 16, 2009 at 10:16 am
I have a W2K3 w/sp2 server with SQL2005 (9.0.4035). I have a Vendor database as follows:
Data.MDF
Data1.NDF
Data2.NDF
Data3.NDF
Data4.NDF
Data5.NDF
Data6.NDF
Data7.NDF
LOG.LDF
The database (filegroups) initially were each 500MB. Each datafile had the default AUTOGROW set at 10%. The files grew and there seems to be a lot of wasted space, as follows:
currently allocated space 24,834.75 MB
availabe free space 13,703.96 MB
I then SHRUNK the database and allowed a couple percent of free space after the shrink. I also reconfigured AUTOGROW for each datafile to 2%.
currently allocated space 12,683.75 MB
availabe free space 1663.75 MB
The database began to grow, as did the FREE SPACE. This was the next day:
currently allocated space 18,065.19 MB
available free space 6072.38 mB
This was the second day after the database SHRINK:
currently allocated space 23,519.63 MB
available free space 11791.13 MB
Why is there so much WASTED FREE SPACE (11791.13 MB) ?
September 16, 2009 at 10:46 am
An object can only be on one filegroup, so depending on what is where, it could be causing growth there. Also, maintenance operations, like index rebuilds, can require tremendous space, so you do not treat this like a Word file. There should be a good amount of free space in the files.
Space is not a day to day management operation, either for you or the server. It should be rare that the db needs to grow. 24GB with 13GB free might be seem like a lot, and it might be a bit much, but if I had 11GB of data, I'd probably have 8GB free at a min, depending on the data growth per month. I want to manually add space every quarter, not more often.
Also 10GB isn't much space. If you're managing space in 10GB increments you're wasting time. Give it space to function, monitor it, and if the space growth seems excessive compared with backup size growth (that is mostly data only), then check it.
September 16, 2009 at 10:52 am
rew-370421 (9/16/2009)I also reconfigured AUTOGROW for each datafile to 2%
This is just adding overhead - if space is needed database will grow the datafile anyway as much as needed - setting autogrow to 2% only forces SQL Server to waste resources by doing the operation once and again.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 16, 2009 at 11:01 am
Thanks Steve.
It just seems like the free space isn't being used. Shouldn't I see the "free space" diminish over a period of time, then the datafiles growth as needed?
Thanks Paul.
Perhaps this is my root problem? What is the "best practice" configurations for the AUTOGROW per Datafiles/FileGroups (Percentage/MBs) ?
September 16, 2009 at 11:05 am
Also, when setting the autogrow on a database, you really should set it to a fixed amount rather than a percentage. By setting it to a percentage, each time it grows it grows by a larger amount. If the database starts at 10 GB using 10% growth, the first time it autogrows, it grows 1 GB; the second time 1.1 GB, etc.
Also, I agree with Steve. You should have sufficient empty space to allow for data/index growth for 3 to 6 months depending on database activity. You should be growing the database manually on a set schedule as needed. The autogrow feature is a good feature to assist in the event of unusal data growth that may not have been anticipated.
September 16, 2009 at 11:09 am
The free space should diminish, and Lynn has a good explanation.
I'd leave the 13GB and watch it. I wouldn't expect you'd have the file sizes grow in the short term.
September 16, 2009 at 11:13 am
Thanks to all for this great information. I'm new to SQL2K5 and am trying to learn it as I go.
September 16, 2009 at 11:18 am
you are welcome and good luck. Let us know if something changes. If you think it's something different, start a new thread and we'll try to help.
September 16, 2009 at 1:11 pm
- also, with these ndf files, it is best to know if they are all allocated to a single filegroup or not. Having multiple files allocated to a filegroup would cause sqlserver to balance the content.
- If you need to, don't just shrink a database, shrink a well choosen (set) of files. You should only do this when your system runs out of space or if you performed a large data cleanup , retaining only a fragment of datavolume you need and you wouldn't expect the data to grow to the starting size in the near future.
- My guess for your situation would be:
-- you have some tables having a clustered index on a uniqueidentifier column and have some large sets of rows added, causing pages splits like hell.
-- index rebuids did run after your shrink causing data to be moved around in your filegroup.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply