February 18, 2010 at 6:27 am
I got the script from one of the sql site for monitoring data growth. that script i have attached here.
one of the production server i have mention data file setting as follows
File growth mdf file -> automatically grow file in 20 MB
File growth ldf file-> automatically grow file in 10 MB
Currently database simple recovery mode.
1. optimization jobs running on weekly basis for RE-building Index, option is-> To change free space per percentage to 10 % ( this jobs is actually space consuming process in sql)
I had monitoring datagrowth for 1 week, growth has chanaged only on sunday because of optimization jobs running on weekly basis. but i could not able seen daily wise.
Can any one suggestion me, Is it require change to precentage(%) in data file setting?
sample report as below
NumPages OrigSize CurSize GrowthAmt Date
1303480 9944.76 9944.76 0.00 MB 10-Feb-10
1303480 9944.76 9944.76 0.00 MB 11-Feb-10
1303480 9944.76 9944.76 0.00 MB 13-Feb-10
1362592 10395.75 10395.75 0.00 MB 16-Feb-10
1362592 10395.75 10395.75 0.00 MB 17-Feb-10
1303480 9944.76 9944.76 0.00 MB 12-Feb-10
1362592 9944.76 10395.75 450.99 MB 14-Feb-10
1362592 10395.75 10395.75 0.00 MB 15-Feb-10
I am sure every 1/2 hr data has been stored & per day 20MB data.
Thanks in advance (SQLDBAs)
February 18, 2010 at 5:20 pm
SQL needs free space in the data files to rebuild indexes approximately 1.5 times the size of the largest index. So if there is no free space in your data files and you rebuild indexes the data file has to grow. Not knowing all the information but my assumptions are:
1. Database is set to Auto Shrink (you should change this option to NOT auto shrink)
2. Data files are set to auto grow
3. There is a 300MB index in the db. 300MB X 1.5 = 450MB growth in the data file
February 19, 2010 at 4:14 am
Thank you Edogg, For your reply.
As per your reply, I got the point how datafile has grown 450MB in optimization jobs.
Can you clear me doubts.
1. If change the setting in db Autoshirnk in OFF, how tlog will be turncate in simple recovery model at every checkpoint.
we don't want change full recovery in db. If change full recovery mode, should take Tlog backup at every time period.
Thanks
February 19, 2010 at 9:35 am
The auto shrink option has nothing to do with recovery model. If you turn auto shrink off and leave the db in simple recovery mode the tlog will continue to be truncated on checkpoint.
Auto shrink is for the datafiles of the database and causes fragmentation.
Check out this blog post by Gail Shaw, it should answer all your questions.
http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/[/url]
February 22, 2010 at 11:05 am
I think auto shrink option applies to all files rather than just data files.
MJ
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply