Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
svrao914
Starting Member
3 Posts |
 Posted - 2014-10-13 : 02:16:27
|
| My database size nearly 15 to 20 GB.We are facing Performance Issues from clients.The Microsoft suggests Change Database Auto growth from Percent to Megabytes.Can any one help me In Megabytes size.what exact value to set ?Is there any formula to Calculate value for Auto growth size?srinivas |
|
|
Shanky
Yak Posting Veteran
84 Posts |
Posted - 2014-10-13 : 06:24:33
|
| Hi,To suggest value for autogrowth one needs to know when and how often did database grow and since we dont have that privilege I will point you to article which would help you in doing so. Please read below article its long but queries in the article would help you seeing DB growth.https://www.simple-talk.com/sql/database-administration/sql-server-database-growth-and-autogrowth-settings/Best place to look for autogrowth is Trace files. These have information about when autogrowth occurred. Its not advisable to keep autogrowth value in percentage. If you want a starting value 250-300 MB can be taken but remember its a purely guessed value Hope this helpsRegardsShankySQL Server MVPhttp://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx |
 |
|
|
svrao914
Starting Member
3 Posts |
Posted - 2014-10-13 : 08:53:49
|
| Hi Shanky,Thank you very much for your response on my Post.I will refer the above article and check with my problem.Regards,Srinivas.srinivas |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
|
svrao914
Starting Member
3 Posts |
Posted - 2014-10-16 : 06:45:39
|
| Hi All,Thanks for your response.One of my client database mdf size nearly 15 to 20 GB and ldf size nearly 35 to 40 GB.We are running Rebuild, Reorganize and Updated statistics maintenance plan Daily.And we configured Transaction log Backups Everyday Every hour.Transaction Log backup size nearly 150 MB Every hour.Then , I have following questions Can I set AutoGrowth value as 150 in MBs ?or Can I set Augrowth value as 1 (Mdf size / hourly transaction size )in percentages?or Can I leave the default settings?Regards,srinivas |
|
|
|
Shanky
Yak Posting Veteran
84 Posts |
Posted - 2014-10-16 : 10:56:48
|
| [\quote]One of my client database mdf size nearly 15 to 20 GB and ldf size nearly 35 to 40 GB.We are running Rebuild, Reorganize and Updated statistics maintenance plan Daily.And we configured Transaction log Backups Everyday Every hour.Transaction Log backup size nearly 150 MB Every hour.Then , I have following questions Can I set AutoGrowth value as 150 in MBs ?or Can I set Augrowth value as 1 (Mdf size / hourly transaction size )in percentages?or Can I leave the default settings?Regards,srinivas[/quote]I always consider a database poorly managed whose log file is greater than data file and I can see reason as you doing Index rebuild, reorganize and update stats JUST LIKE THAT. I guess you must be doing it though Maintenance plan which is total crap. Create your own intelligent plan which rebuilds only indexes which are fragmented and reorganize only indexes which has fragmentation between 10 and 30. Alos note index rebuild with full scan on col will update stats for that col.As a started you can set but definitely not a correct value unless you find oneHope this helpsRegardsShankySQL Server MVPhttp://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-10-16 : 12:41:35
|
| I would change your tlog backup job to be every 15 minutes which should help with the tlog size. After making the schedule change, go ahead with a shrink on the ldf file and then check it after all your maintenance runs. See if it has grown. If the size to shrink it to, I would set it to at least the size of the largest index, plus a little bit extra.I generally will start out with these autogrowth settings and then adjust as needed later as I know more about the system:Data files - 512mbLog file - 256mb (and with instant initialization configured)Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
Database Autogrowth