Auto Growth Option in SQL Server Database Part-1

Hi Guys,

Thanks a lot friends for your wishes for my certification completion Blog here.

There was a discussion couple of days back with one of my colleague DBA about the "Auto growth" option in SQL Server Database.

This topic came into consideration when one of my re-indexing job got failed due to less disk space on the log drive. As we are aware of rebuild requires  sufficient amount of disk space for the log file growth (We will discuss on separate post "Behind the scene- while Re-indexing").This will be a two part series.

We can check the "Auto growth" option under 

Right Click on Database, Next Properties ,Next Files, Next Autogrowth / Maxsize Column. 

Auto- Growth Properties

Here you will see depending upon total number of Data and Log Files you have in your Database. 

By default, it will inherited the value from Model Database but the Log growth will limit up to 2,097,152 MB (2 TB). So now the question is should we limit this to by default value? or we should change the setting? 

The answer is "It Depends" (Just like most of the answers in SQL Server). Well, it depends up to scenario to scenario. There are many factors come into consideration before changing the value in Production environment.

1.  How frequently is my log growing?

2.  What are the operations are happening in my Database?

3.  I have multiple Log files in different Drive. Still my log file will grow?

4.  Do we take Log backups?

What triggers to grow the Log file rapidly:

a. If your recovery model is on "Full" and there is a bulk operation such as BCP  it will capture each       and  every transaction in log. 

b. Even in rebuilding operations the log files will grow rapidly.

c. Recovery model is "Full" and there is no Log Backups taken regularly.   

So as you can see and as I said it depends upon many factors. So before changing anything in Production environment you should be well aware of all the scenarios.

Following are the parameters we need to set in "Auto Growth" for a Database?

1. We can set this option for both Data and Log File by two types:

a. In Percent

b. In Megabytes

2. We can set the Maximum File Size to

a. Limited

b. Unlimited

With the help of below query we can find out this setting for all the Databases:

--auto growth percentage for data and log files

Select DB_NAME(files.database_id) database_name, files.name logical_name, 

CONVERT (numeric (15,2) , (convert(numeric, size) * 8192)/1048576) [file_size (MB)],

[next_auto_growth_size (MB)] = case is_percent_growth

    when 1 then CONVERT(numeric(18,2), (((convert(numeric, size)*growth)/100)*8)/1024)

    when 0 then CONVERT(numeric(18,2), (convert(numeric, growth)*8)/1024)

end,

is_read_only = case is_read_only 

    when 1 then 'Yes'

    when 0 then 'No'

end,    

is_percent_growth = case is_percent_growth 

    when 1 then 'Yes'

    when 0 then 'No'

end, 

physical_name

from sys.master_files files

where files.type in (0,1)

and files.growth != 0

Now as a DBA needs to set these parameters after knowing the behavior of the Database in given environment. By default it will inherit the values from your Model Database.

We will continue in the next post Part 2 the cases which best suits for parameter 1. 

Just to share with you last year on 22nd June'14 I started blogging and it's now 1 Year. Special Thanks to Akhilesh Humbe for the guidance and Thank you friends for reading my post. 

If you have any suggestion to improve this blog feel free to comment. Suggestion are most and always welcome. 

Keep in touch... And Happy Learning....

I appreciate and thank you for reading this post.