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.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Database Autogrowth

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 helps

Regards
Shanky
SQL Server MVP
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx
Go to Top of Page

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
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-10-16 : 01:50:12
For SQL LOG File autogrow performance troubleshooting - check http://www.sqlserver-dba.com/2012/04/sql-log-file-autogrow-performance-troubleshooting.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

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
Go to Top of Page

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 one

Hope this helps

Regards
Shanky
SQL Server MVP
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx
Go to Top of Page

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 - 512mb
Log file - 256mb (and with instant initialization configured)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -