How to determine the SQL server Autogrowth value ?

Posted on 2014-08-12
Last Modified: 2014-08-20
How to determine the SQL server Auto growth value ?

I have the following values in my production database. This is a default values and I did not change this values.

File Name       Initial Size            Auto Growth
MDF                  115MB                By 1 MB, Unlimited
LDF                   24MB                   By 10 percent,  Limited to  2,097,152MB

For MDF file Auto Growth value is By 1 MB and Unlimited.  Why SQL server set the value to 1MB, What will happen if i insert bulk values in the database. Should i have to change this value OR SQL server will handle itself ?

For LDF auto growth is 10% and limited to 2,097,152MB. What will happen once its reach 2,097,152MB ?
Should i have to change the auto growth  value OR SQL server will handle itself ?
Question by:Varshini S
    LVL 25

    Assisted Solution

    by:Lee Savidge
    It's called Autogrowth. It automatically grows as SQL needs it. Knowing what value to set here comes with experience and knowledge of the growth potential of your database. It depends on how often the database needs to grow. SQL will handle it. I've not had cause to alter this often but read this for more info.
    LVL 26

    Accepted Solution

    Here is a view I use for general information about database files, autogrowth included:
    CREATE view [dbo].[vwGetDatabasesInfo]
    	select * from [dbo].[vwGetDatabasesInfo]
    	cast(db.Name as varchar(50)) AS DatabaseName, 
    	cast(DATABASEPROPERTYEX(db.Name, 'Status') as varchar(10)) AS DBStatus,
    	cast(DATABASEPROPERTYEX(db.Name, 'Recovery') as varchar(10)) AS Recovery,
    	cast(DATABASEPROPERTYEX(db.Name, 'Updateability') as varchar(15)) AS Updateability,
    	cast(DATABASEPROPERTYEX(db.Name, 'UserAccess') as varchar(15)) AS UserAccess,
    	cast(round(RowSizeMB,2) as decimal(10,2)) as RowSizeMB,
    	cast(round(LogSizeMB,2) as decimal(10,2)) as LogSizeMB,
    	cast(round(StreamSizeMB,2) as decimal(10,2)) as StreamSizeMB,
    	cast(round(TextIndexSizeMB,2) as decimal(10,2)) as TextIndexSizeMB,
    	cast(round(TotalSizeMB,2) as decimal(10,2)) as TotalSizeMB,
    	fl.[file_id], as file_logical_name,
    	fl.physical_name as physical_file_name,
    	fl.type_desc as file_type,
    	cast(round(fl.size*8.00/1024,2) as decimal(10,2)) as physical_file_sizeMB,
    	fl.state as file_state,
    	cast(fl.max_size as bigint)*8/1024 as file_max_sizeMB,
    	fl.growth*8/1024 as file_growth,
    	left(case fl.is_percent_growth when 1 then '%' else 'MB' end,2) as file_growth_type
    	master.sys.databases db 
    	inner join
    	( -- get the db sizes
    			(CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
    			(CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
    			(CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
    			(CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB,
    			(CAST(mfrows.RowSize AS FLOAT)*8)/1024+
    			(CAST(mflog.LogSize AS FLOAT)*8)/1024+
    			isnull((CAST(mfstream.StreamSize AS FLOAT)*8)/1024,0)+
    			isnull((CAST(mftext.TextIndexSize AS FLOAT)*8)/1024,0) as TotalSizeMB
    		FROM master.sys.databases db
    			LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM master.sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id
    			LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM master.sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id
    			LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM master.sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id
    			LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM master.sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id
    	) dbsize
    		on dbsize.database_id=db.database_id
    	inner join master.sys.master_files fl
    		on fl.database_id=db.database_id
    	left join sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
    		on divfs.database_id = fl.database_id

    Open in new window

    If you set teh autogroouth in MB value and it is too small the growth process will take longer and also it will cause the number of VLFs in the log files to be bigger with bad consequences in the restore performance.

    Article about VLF count and database size.:

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

         When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now