[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to determine the SQL server Autogrowth value ?

Posted on 2014-08-12
2
Medium Priority
?
475 Views
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 ?
0
Comment
Question by:Varshini S
2 Comments
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 1000 total points
ID: 40255794
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.

http://www.sqlservercentral.com/blogs/steve_jones/2011/12/02/autogrow-guidelines/
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 1000 total points
ID: 40255961
Here is a view I use for general information about database files, autogrowth included:
CREATE view [dbo].[vwGetDatabasesInfo]
as
/*
	select * from [dbo].[vwGetDatabasesInfo]
--*/
SELECT 
	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],
	fl.name 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
	,num_of_reads
	,num_of_bytes_read
	,io_stall_read_ms
	,num_of_writes
	,num_of_bytes_written
	,io_stall_write_ms
	,io_stall
from 
	master.sys.databases db 
	inner join
	( -- get the db sizes
		SELECT
			db.database_id,
			(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.:

http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

834 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