August 22, 2018 at 11:59 am
Even if you had a tried and proven method of managing file growth, would you still think it ok to leave auto growth set at 1Mb and no limit on file growth?
...
August 22, 2018 at 2:03 pm
Normally I don't like answering "It Depends" however this is one of those times. 😀
How much disk space do you have? Remember file growths will tank performance so is this an OLTP system or OLAP? Personally I wouldn't leave autogrowth at 1MB and no limit as one day it will fill the disk and then you're going to have problems. What size is the database? Have you performed any baselining on it? Do you know how much it will grow in 1week/month/year?
qh
August 22, 2018 at 2:41 pm
HappyGeek - Wednesday, August 22, 2018 11:59 AMEven if you had a tried and proven method of managing file growth, would you still think it ok to leave auto growth set at 1Mb and no limit on file growth?
Normally I wouldn't use 1 MB for a growth increment but I am sure there is some scenario which would be an exception.
But why would you post up here for a sanity check? 🙂
Sue
August 22, 2018 at 6:19 pm
Is your database < 50MB? Then sure. Otherwise, no.
August 22, 2018 at 10:11 pm
Sue_H - Wednesday, August 22, 2018 2:41 PMHappyGeek - Wednesday, August 22, 2018 11:59 AMEven if you had a tried and proven method of managing file growth, would you still think it ok to leave auto growth set at 1Mb and no limit on file growth?Normally I wouldn't use 1 MB for a growth increment but I am sure there is some scenario which would be an exception.
But why would you post up here for a sanity check? 🙂Sue
Thanks Sue, I'm in a short term contract, they have a full time DBA, I have limited rights but this happens to be something I can see. The servers perform abysmally, databases in the order of 10s of GB's and everyone wonders why; I have not questioned the DBA as to how he manages file growth, just trying to anticipate any possible reaction if I do.
...
August 23, 2018 at 4:56 am
10s of GB of DB set to autogrow in 1 MB increments...?
Yup, that's definitely an epic fail on any sanity check, but of course, this will only actually make a difference if you actually have autogrowth events on your DB.
If you right click on one of these databases in SSMS, you may be able to access the "Disk usage" standard report
(Right click on DB --> Reports --> Standard Reports --> Disk Usage).
Right below the neat little graphical representation of % space usage, this report will tell you if you've had any recent autogrow events, and if so, you will see a line saying "Data/Log Files Autogrow/Autoshrink Events" with a little + on the left.
Click this little plus and SQL Server will flat out tell you exactly what autogrows have been performed recently and how much time it spent on each one of those.
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
August 23, 2018 at 6:09 am
HappyGeek - Wednesday, August 22, 2018 10:11 PMThanks Sue, I'm in a short term contract, they have a full time DBA, I have limited rights but this happens to be something I can see. The servers perform abysmally, databases in the order of 10s of GB's and everyone wonders why; I have not questioned the DBA as to how he manages file growth, just trying to anticipate any possible reaction if I do.
Those are interesting but sometimes aren't too fun - especially if it wasn't the DBA who wanted some help. Are you in for just the performance issues? It can be challenging if the DBA really doesn't want you to find issues. And/or having you come in was someone else's idea. On the growth, if you have access to the trace files those would show how often what files are growing. And if you see hundreds of those in a row, then it becomes pretty obvious.
Are you able to run something like Brent Ozar's sp_blitz? That one is pretty good to use in those kind of scenarios. Sometime when using something like that it can be more along the lines where you are just presenting results of a query. That can feel less threatening than just documenting the issues if you can get the right "spin" on presenting it.
Sue
August 23, 2018 at 6:30 am
Sue_H - Thursday, August 23, 2018 6:08 AMThose are interesting but sometimes aren't too fun - especially if it wasn't the DBA who wanted some help. Are you in for just the performance issues? It can be challenging if the DBA really doesn't want you to find issues. And/or having you come in was someone else's idea. On the growth, if you have access to the trace files those would show how often what files are growing. And if you see hundreds of those in a row, then it becomes pretty obvious.
Are you able to run something like Brent Ozar's sp_blitz? That one is pretty good to use in those kind of scenarios. Sometime when using something like that it can be more along the lines where you are just presenting results of a query. That can feel less threatening than just documenting the issues if you can get the right "spin" on presenting it.Sue
Very short term in a support team, helping with their BI solution by way of a change, checked the report suggested by Vegard and yep autogrowth 1MB every minute or so.... urrrmmmmm
...
August 23, 2018 at 7:04 am
HappyGeek - Thursday, August 23, 2018 6:30 AMVery short term in a support team, helping with their BI solution by way of a change, checked the report suggested by Vegard and yep autogrowth 1MB every minute or so.... urrrmmmmm
Ouch. I've done those where you are brought it for one thing but you see something of a mess with how things are setup, configured. I've usually included some of that in a final documentation. Whether they pursue it or not is up to them but personally I didn't want to walk away from those issues. I have been called to come back on most of those to address the issues. In your situation if things run like crap and everyone wonders why, people know there is a problem.
If you could view that report, it gets the information from the trace file using fn_trace_gettable. So you have access to the trace file in case you need that at some point.
Sue
August 23, 2018 at 7:30 am
HappyGeek - Wednesday, August 22, 2018 11:59 AMEven if you had a tried and proven method of managing file growth, would you still think it ok to leave auto growth set at 1Mb and no limit on file growth?
A better question would be, if you have a "tried and proven method of managing file growth, then why on this good Green Earth would you ever even consider leaving file growth set to just 1MB except for very special cases?
Heh... and just read back through this post... doing short tem work for people leads to some interesting times. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply