Day 8: Auto grow taking time -Information

We observed that very often we get information/error at error log something like this:

 

  2011-10-21 12:02:25.54 spid876   Autogrow of file ‘XXX’ in database ‘XXX’ took 73719 milliseconds.  Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

 

Generally, the database file (data/log) is set to auto grow and growth rate sets to 10%. By default data file is creating with

 

So with 10% growth will be good for smaller size, what if size become huge of size 50gb or 100gb so 10% of that will be 5gb or 10gb, so to grown up that big size will take quite good time and could also cause performance issue.

 

Resolution:-

 

>> Change the growth rate of this database from 10% to smaller fixed size (eg 10mb -100mb) depending upon the transactions.

>> Standard practice is if you know your system you could manually maintain the size, and keep it increasing at non peak hrs periodically (with safer side

keep auto-grow and growth rate on)

Advertisements
This entry was posted in Troubleshooting and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s