InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 14:52:11 GMT20http://www.sqlservercentral.com/Forums/Topic1233094-24-1.aspxThanks John that's an interesting point. The disk is shared with a read only copy of our production db(the one we've been discussing) that gets restored every early morning from the current backup. The disk is 350G with 73G used.Tue, 10 Jan 2012 08:30:19 GMTTJ-356724http://www.sqlservercentral.com/Forums/Topic1233094-24-1.aspxUnrestricted file growth will depend on whether your database shares the disk with other databases that may also want to grow. If it does, then it's wise to set a maximum growth for each file on the disk in case one of them suddenly grows and tries to consume the whole disk.JohnTue, 10 Jan 2012 07:25:55 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1233094-24-1.aspxYes.Tue, 10 Jan 2012 07:23:05 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1233094-24-1.aspxThanks Ninja. So as you and John suggested the final settings on SQL 2000 autogrowth=Yes In Megabytes 500-2000 Unrestricted file growth = Yesmonitor using sp_spaceused or via scipt and keep doing what we're doing with the backups. Tue, 10 Jan 2012 07:21:39 GMTTJ-356724http://www.sqlservercentral.com/Forums/Topic1233094-24-1.aspx[quote][b]Tim Jacobs-356724 (1/10/2012)[/b][hr]Ninja, I don't want them to grow bad just trying to understand if the 3% is even revelant at this time given the free space. Some literature suggest never using autogrowth but monitor and adjust accordingly. I do appreciate the guidance from you and John.[/quote]Never RELY on autogrow. It's an insurrance against unforseen data spikes or other problems.You should set it to 500 to 2000 mb and move on to your next task. That way the db(s) won't break (running out of space does that, no more transactions can happen, but read is ok).Tue, 10 Jan 2012 07:08:32 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1233094-24-1.aspxNinja, I don't want them to grow bad just trying to understand if the 3% is even revelant at this time given the free space. Some literature suggest never using autogrowth but monitor and adjust accordingly. I do appreciate the guidance from you and John.Tue, 10 Jan 2012 07:03:44 GMTTJ-356724http://www.sqlservercentral.com/Forums/Topic1233094-24-1.aspx[quote][b]Tim Jacobs-356724 (1/10/2012)[/b][hr]So in effect then data and log files are not growing because they have so much free space and the 3% growht is not revelant at the time.[/quote]Yes.Why do you want them to grow so bad?? ;-)Tue, 10 Jan 2012 06:47:10 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1233094-24-1.aspxSo in effect then data and log files are not growing because they have so much free space and the 3% growht is not revelant at the time.Tue, 10 Jan 2012 06:45:27 GMTTJ-356724http://www.sqlservercentral.com/Forums/Topic1233094-24-1.aspx[quote][b]Tim Jacobs-356724 (1/10/2012)[/b][hr]Thanks Ninja. Yes it's SQL 2000. So everyone would suggest that I change from percent to file growth in megabytes(500-2000_ but autogrowth would still be on.[/quote]Yes.You need to test to make sure it can happen within 1-2 seconds so that the users don't wait on it.Then search the internet for monitoring scripts. I have one but it's for 2K5+.Tue, 10 Jan 2012 06:44:37 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1233094-24-1.aspxThanks Ninja. Yes it's SQL 2000. So everyone would suggest that I change from percent to file growth in megabytes(500-2000_ but autogrowth would still be on.Tue, 10 Jan 2012 06:42:36 GMTTJ-356724http://www.sqlservercentral.com/Forums/Topic1233094-24-1.aspxYes, that's right. But of course once it's consumed or reduced, it's closer to 0% than to 40 or 99.JohnTue, 10 Jan 2012 06:40:06 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1233094-24-1.aspxYes.Tue, 10 Jan 2012 06:38:47 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1233094-24-1.aspxI just need to understand one point. If I have free space = 99% or 40% doesn't that free space have to be consumed(or show as being reduced) before autogrowth kicks in?Tue, 10 Jan 2012 06:36:55 GMTTJ-356724http://www.sqlservercentral.com/Forums/Topic1233094-24-1.aspxFree space isn't relevant since autogrow is what happens when the free space runs out. It doesn't force any space to be used. 1% is probably rather low, but in any case it's usually best to use a fixed number of MB instead of a percentage (maybe 500 to 1000 given the size of your database). sp_spaceused is a good place to start for monitoring. There are more sophisticated techniques, which you should be able to read about on the internet.JohnTue, 10 Jan 2012 06:22:39 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1233094-24-1.aspx[quote][b]Tim Jacobs-356724 (1/10/2012)[/b][hr]Thanks John. Given the free space in the files should 1% auto be ample? Isn't this forcing the free space in each file to be used? Can you suggest proper way to monitor the file sizes before alter db command is required to increase the sizes?[/quote]Do you have dbs with more than 1 data file?1% is too low, you should set this to 0.5-2 GB (whatever your system can grow without taking more than a few seconds).But first you must monitor, autogrow should be your final, final last failsafe.Edit, can you confirm you are on sql 2000?Tue, 10 Jan 2012 06:19:40 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1233094-24-1.aspxThanks John. Given the free space in the files should 1% auto be ample? Isn't this forcing the free space in each file to be used? Can you suggest proper way to monitor the file sizes before alter db command is required to increase the sizes?Tue, 10 Jan 2012 06:12:20 GMTTJ-356724http://www.sqlservercentral.com/Forums/Topic1233094-24-1.aspxProbably best to monitor file size proactively but set autogrowth just in case. The fragmentation levels I've seen used most often are to reorganise indexes above 5% and rebuild above 30%. Don't really know about page count - I think maybe 1000 rows is reasonable. The exact figure shouldn't matter that much because it won't take very long at all to rebuild indexes on such small tables.Tue, 10 Jan 2012 06:05:43 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1233094-24-1.aspxI am not a DBA but get into it on a periodically basis. We have db size of 50G with 25.3G free or 50%. Breakdown is as follows: Data 42G allocated with 17G free or 40%. Log 8.3G with 8.2 free or 99%. DB backup every day. Trans log backup everyday from 6 am to 11 pm. Only indexes with pages > 10 and fragmentation > 10 are reindexed once per week(this should probably be changed). The current(legacy) auto-growth is set at 3% for both data and log. We do not expect rapid db growth but can setup scripts to monitor. Given these conditions should auto-growth be set to 0 for both data and log? What is the most reasonable page count and fragmentation level to do DBCC DBREINDEX for the selected table\index?Tue, 10 Jan 2012 05:46:09 GMTTJ-356724