October 2, 2008 at 3:43 am
Yeah, that sounds sooo familiar. Glad I'm not the only one with that problem. @=)
The main database I oversee was so not meant for the use people are putting it to these days. @=)
October 2, 2008 at 10:06 am
Jeff: Don't ever let the defaults for auto-growth on a database occur... it takes 73 disk fragments just to reach 1GB using the default settings.
I believe you are way under there Jeff. IIRC, the default is 10MB data file with 1MB growth increment. That means about 1000 file fragments to hit 1GB.
I recommend my clients target 12-18 months growth (including index sizes) and set their database to that size up front, then re-evaluate every 3-6 months or so. Autogrowth is still enabled just in case.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 2, 2008 at 9:22 pm
TheSQLGuru (10/2/2008)
Jeff: Don't ever let the defaults for auto-growth on a database occur... it takes 73 disk fragments just to reach 1GB using the default settings.I believe you are way under there Jeff. IIRC, the default is 10MB data file with 1MB growth increment. That means about 1000 file fragments to hit 1GB.
I recommend my clients target 12-18 months growth (including index sizes) and set their database to that size up front, then re-evaluate every 3-6 months or so. Autogrowth is still enabled just in case.
Nope... not wrong... do the math...
[font="Courier New"]
Growth Spurt Size after growth of 10%
0 1,000,000
1 1,100,000
2 1,210,000
3 1,331,000
4 1,464,100
5 1,610,510
6 1,771,561
7 1,948,717
8 2,143,589
9 2,357,948
10 2,593,742
11 2,853,117
12 3,138,428
13 3,452,271
14 3,797,498
15 4,177,248
16 4,594,973
17 5,054,470
18 5,559,917
19 6,115,909
20 6,727,500
21 7,400,250
22 8,140,275
23 8,954,302
24 9,849,733
25 10,834,706
26 11,918,177
27 13,109,994
28 14,420,994
29 15,863,093
30 17,449,402
31 19,194,342
32 21,113,777
33 23,225,154
34 25,547,670
35 28,102,437
36 30,912,681
37 34,003,949
38 37,404,343
39 41,144,778
40 45,259,256
41 49,785,181
42 54,763,699
43 60,240,069
44 66,264,076
45 72,890,484
46 80,179,532
47 88,197,485
48 97,017,234
49 106,718,957
50 117,390,853
51 129,129,938
52 142,042,932
53 156,247,225
54 171,871,948
55 189,059,142
56 207,965,057
57 228,761,562
58 251,637,719
59 276,801,490
60 304,481,640
61 334,929,803
62 368,422,784
63 405,265,062
64 445,791,568
65 490,370,725
66 539,407,798
67 593,348,578
68 652,683,435
69 717,951,779
70 789,746,957
71 868,721,652
72 955,593,818
73 1,051,153,200 [/font]
Think "compound interest". 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
October 3, 2008 at 8:19 am
>>Think "compound interest".
Think "read my post"! 😀
The default data file growth is ONE MEGABYTE, not TEN PERCENT (at least on all the servers I recall installing). 10% is the default growth on the LOG FILE. I believe these come from the defaults on the model database.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 3, 2008 at 8:54 am
TheSQLGuru (10/3/2008)
>>Think "compound interest".Think "read my post"! 😀
The default data file growth is ONE MEGABYTE, not TEN PERCENT (at least on all the servers I recall installing). 10% is the default growth on the LOG FILE. I believe these come from the defaults on the model database.
That's my recollection as well (on the growth factors). The default size for a new database is 2MB, not 10, though.
In either case - the case remains - think about the size your data file and log files should be, and size them appropriately. Don't rely on autogrowth to do your job, and make the files as large as you can afford to make them.
Does that sound about right?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 3, 2008 at 4:58 pm
Actually, I was talking about SQL Server 2000 'cause I'm still stuck in that world for better or worse. I forgot this was a 2k5 forum.
I just setup a brand new database using 2k5... the defaults were as follows...
MDF - 3MB initial size - 1MB growth rate
LDF - 2MB initial size -10% growth rate
Heh... worst of both worlds. Kevin was right...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 3, 2008 at 5:01 pm
TheSQLGuru (10/3/2008)
>>Think "compound interest".Think "read my post"! 😀
The default data file growth is ONE MEGABYTE, not TEN PERCENT (at least on all the servers I recall installing). 10% is the default growth on the LOG FILE. I believe these come from the defaults on the model database.
You're correct, of course... I'm still thinking in terms of SQL Server 2000 'cause I'm still pretty much stuck in that world. Sorry for the mixup. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
October 3, 2008 at 5:48 pm
Jeff Moden (10/3/2008)
Actually, I was talking about SQL Server 2000 'cause I'm still stuck in that world for better or worse. I forgot this was a 2k5 forum.I just setup a brand new database using 2k5... the defaults were as follows...
MDF - 3MB initial size - 1MB growth rate
LDF - 2MB initial size -10% growth rate
Heh... worst of both worlds. Kevin was right...
My defaults were different, which puzzled me for a sec. The defaults are actually based on whatever MODEL is, so if MODEL is 3MB/2MB then that's the starting size. Also - it would pull its auto-growth settings from there too. I just changed model, and now my starting sizes are 100mb/100mb with matching auto-growth settings.
I always keep forgettting about that darn database.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply