Autogrow on SQL Server

[Former Member]

Hi Gurus,

I have setted up a new system in which i have initialized 20G of datafile each, now the problem is by default it is on autogrow option.

the data files are filling up very fast and soon it will be over 20G, I have restricted one file from SQL enterprise manager by disabling autogrow options.

As soon as i disabled it, the file from SAP db02 transaction is show the status as yellow traingle, Is this normal?? will it turn red after a while??

I would be very happy to add files manually everytime rather than autogrow option.

Help

[Former Member · Accepted solution]

Hi All,

Thanks a lot for your valuable inputs. I have enabled the autogrowth on each of 20G file and autoextend by 1 MB.

I have also added few more datafiles having 20G each in similar fashion.

[Former Member · Accepted solution]

Hi All,

Thanks a lot for your valuable inputs. I have enabled the autogrowth on each of 20G file and autoextend by 1 MB.

I have also added few more datafiles having 20G each in similar fashion.

My concern is what if the drive becomes full, In that case what am i supposed to do?? Somewhere down the line i will have to restrict the datafile due to insufficient drive space.

[former_member184473 · Accepted solution]

Hello John,

I think you should increase a little bit the autogrow size. Setting it for 1MB you may face a lot of fragmentation.

In the mentioned paper page 38 we can see:

Setting autogrowth
The autogrowth rate set by SAP when creating the database files is 10%, which is a good extension size. However, in cases where database files become larger and eventually go into a few hundred gigabytes, consider setting the extension to a fixed size.

You have your datafiles in a particular drive full, you can remove the autogrow option for those files and add more file in another drive.

Also, consider the following from paper page 34:

Manageability
A DBA should regularly check the free space of each data file.

This means you should manage the sizes of your databases and not rely on autogrow to extend the files in normal operations.

Regards,

Eduardo Rezende

[Former Member · Accepted solution]

In my opinion you should always go for a monitoring system for your (SAP) environment. I would use tools like Nagios (free) or System Center (Microsoft) to Monitor the server (process, cpu, diskspace e.g.) and the services like SAP or SQL. When you use Nagios for example use plugins to graph each process, service. With this you can get a view from your system.

Example:

If you want to check the diskspace monitor it and graph it. Over a year you can now see on an easy way how much grow you had in the last year or if you do it with cpu you can see your cpu utilisation over a period of time and if that may have changed or not.

[Former Member · Accepted solution]

Hi,

Thanks for all those replies, I have increased the growth option from 1MB to 60MB for all the files.

What is the limit of number of datafiles in SQL.??

Is it good to have too many datafiles with less size?? OR is it good to have less files with Huge size.??

[Former Member · Accepted solution]

check following from the earlier reply.

page 34

Manageability. For best performance, the data files should be equally filled. This feature, called proportional fill, is described later in this paper. It does not work optimally after a single data file is full. A DBA should regularly check the free space of each data file. Having too many data files results in huge administration overhead. Though SQL Server can support 32,767 files per database, itu2019s not a good idea to approach this number of files. At this point in time, we suggest that a reasonable limit would be 64 data files.

[Former Member · Accepted solution]

Thanks for all your help.

[Former Member]

For performance reasons it is recommended that data files are balanced for i/o performance.

i.e. that all data files have an equal mount of data in them, and that data files grow evenly.

Best practice therefore would be:

(1) That you monitor & manually grow all data-files evenly when they get close to reaching the limit, but also

(2) That you leave autogrow on.

Why should you manually grow but also leave autogrow on?

Leaving autogrow on provides a backstop in cases there is a large (unexpected) growth in your database to prevent the system from stopping due to no space available in the database.

It is NOT good practise to add additional datafiles as this leads to unbalanced i/o across the datafiles.

Please especially read SAP Note#1238893 - Proportional File Auto-Growth with SQL Server 2008 as this note provides an explanation of this and also offers a workaround for SQL Server 2008 toi enable automatic growth across all data files (which means you no longer need to manually grow data files). Even if you don't run SQL 2008 please read the note as it provides a good explanation of auto-growth & i/o performance

You don't mention what SQL release you are on (but transaction DB02 and Enterprise Manager suggests you are on an older SAP release with SQL 2000).

Thanks.

Colin

[Abuzar]

Hi Colin,

our SAP Prodution system is having 4 Data files. Each data file size is 125 GB. Auto Grow is enbale with enough space to grow.

Free percentage of eache data file is different.

FILENAME SIZE(MB) FREE %

P11DATA1 128,000 4.00

P11DATA2 128,000 39.00

P11DATA3 128,000 22.00

P11DATA4 128,000 20.00

P11LOG1 20,480 50.00

Some time in DBACOCKPIT IO ms/op of a particuar data file showing very high . like Data3 IO is 600 ms/op.

Databse is SQL 2008 R2 CU1.

Kindly suggest you valuable inputs to rectify this.

Thanks.

Abuzar.

[Former Member]

HI

You should manually manage data files free space and also it recommended to turn the auto grow option ON, just in case if data file is ran out of free space completely and it has to grow.

how many data files you have, usually it is 3-16 files depending upon the size of the system.

Thanks

Mushtaq

[Former Member]

Hi Mushtaq,

Currently I have 8 datafiles (initial size of 20G each having 30% free space) in which i have disable autogrow option from SQL Server enterprise manager.

Does this have any impact on the database?

Should i disable the autogrow option for the existing datafiles?

I am ready to manage the space by manually adding data files with the limit of 20G.

[former_member184473]

Hello John,

As Mushtaq mentioned if you turn off the autogrow option you may ran out of space but if you manage to have enough free space into your datafiles there is no impact to the database.

Regards,

Eduardo Rezende

[Former Member]

Just one more query.

Suppose i am having a datafile1 of 20G with auto growth as off and it is completely full (no free space left).

Is this norma??

In that case all i have to do is add onether datafile2 with sufficient free space

Or do i have to extend the datafile1?? Or can I leave the datafile as it is without worrying since i will already be having datafile2 with sufficient free space?

[former_member184473]

Hello John,

If you have all your datafiles (independent of the number) completely full, when your system need any space it will crash.

If you add a new datafile, you will prevent this crash.

Just keep in mind that having your datafiles equally filled is important for your system performance.

http://www.sdn.sap.com/irj/sdn/mss?rid=/library/uuid/4ab89e84-0d01-0010-cda2-82ddc3548c65

page 34
Manageability. For best performance, the data files should be equally filled. This feature, called proportional fill, is described later in this paper. It does not work optimally after a single data file is full. A DBA should regularly check the free space of each data file. Having too many data files results in huge administration overhead. Though SQL Server can support 32,767 files per database, itu2019s not a good idea to approach this number of files. At this point in time, we suggest that a reasonable limit would be 64 data files.

Regards,

Eduardo Rezende