Archive for the ‘Auto-growth’ Category

How to view the database auto-growth settings and correct them?

Auto-growth is the process which the SQL Server engine expands the size of the database data or log files when they are out of space. The growth size will depend on the settings you have specified for the file growth options for your databases. There are three settings that you can set for the file growth by:

  • Specific size
  • Percentage of the current size
  • Not grow at all

In addition, you have two options when configuring the maximum size for the database data or log files:

  • Unrestricted growth: the files will keep on growing until you are out of disk space
  • Restricted growth: you specify the maximum file size in MB for the files

You should always set the initial size for the database data and log files appropriately to prevent the auto-growth events. When an auto-growth event occurs, the negative impacts can be:

  • Locking and blocking: when auto-growth occurs, a database level lock is required. All the processes will need to wait until auto-growth completes.
  • Fragmentations: when auto-growth occurs, SQL Server will need to find additional disk space somewhere in the disk drive(s) and usually the disk space will not physically next to the existing database file space.

When creating a new database, you should always establish the auto-growth settings in addition to setting the initial sizes correctly. By default, new databases are created based on the settings of the Model system database. The Model database has the default settings of auto-growth by 1 MB for data files and 10% for log files. Both have unrestricted growth. The files will grow until the disk is full. Imagine you have a transaction intensive OLTP database and it’s over 500GB for the data file and 200GB for the log file and the auto-growth setting is using the default settings. Whenever an auto-growth event is needed, the data file will grow by 1MB and the log file will growth by 2,000MB or more (10% of the existing log file size). It’s definitely not pretty as the auto-growth events for the data file will occur constantly as it grows by 1MB.

Now, if the SQL Server instance has been in production for over 2 years and there are over 20 databases, how you can quickly identify the auto-growth settings for each of the databases without going through each one by right-clicking the databases and selecting and viewing the properties of the databases and then browsing to the Files section and finally clicking the ellipses button to view the auto-growth setting? The following script can help you:

-- auto growth settings for data and log files
select DB_NAME(mf.database_id) database_name
, mf.name logical_name
, CONVERT (DECIMAL (20,2) , (CONVERT(DECIMAL, size)/128)) [file_size_MB]
, CASE mf.is_percent_growth 
    WHEN 1 THEN 'Yes'
    ELSE 'No'
  END AS [is_percent_growth]
, CASE mf.is_percent_growth
	WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
	WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
  END AS [growth_in_increment_of]
, CASE mf.is_percent_growth
    WHEN 1 THEN CONVERT(DECIMAL(20,2), (((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
    WHEN 0 THEN CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL, growth)/128)) 
  END AS [next_auto_growth_size_MB] 
, CASE mf.max_size
	WHEN 0 THEN 'No growth is allowed'
	WHEN -1 THEN 'File will grow until the disk is full'
	ELSE CONVERT(VARCHAR, mf.max_size)
  END AS [max_size]
, physical_name  
from sys.master_files mf

You can identify how often an auto-growth event occurs by:

  • Querying the default trace for the “Data File Auto Grow” and “Log File Auto Grow” events
  • If the default trace is not running, then run a trace to capture the “Data File Auto Grow” and “Log File Auto Grow” events

Auto-Grow Events

It is highly recommended that you pre-size the data and log files to eliminate or reduce the auto-growth events. You can modify the initial size, max size, and the file growth settings of the database data and log files by using the following code:

ALTER DATABASE DatabaseName
MODIFY FILE
(
NAME = FileName
, SIZE = InitialSize
, MAXSIZE = RestrictedMaxSize
, FILEGROWTH = SizeToGrowBy
)

For example, to modify the initial size, max size, and the file growth settings for the data and log files for the TestDB:

-- modify the data file settings
ALTER DATABASE TestDB
MODIFY FILE
(
NAME = TestDB
, SIZE = 60MB
, MAXSIZE = 100MB
, FILEGROWTH = 5MB
) 
-- modify the log file settings
ALTER DATABASE TestDB
MODIFY FILE
(
NAME = TestDB_log
, SIZE = 60MB
, MAXSIZE = 100MB
, FILEGROWTH = 12%
)