This blog offers a solution to finding a suitable process to manage the growth of SQL database data files, instead of leaving them unmanaged in the default auto-growth configuration.

 

The default settings for database initial size and additional characteristics are taken from the properties of the model database. The default additional characteristics include the auto-grow settings, which come into effect when the data files occupied by the database and transaction log file run out of space. When space for either the database or the transaction log runs out, the SQL server will grow the data file according to the properties of the auto-grow configuration. This is the usual and default behaviour of SQL server.

As the SQL database continues to grow, there comes a time when either the auto-grow happens many times a day, or it happens as a large file extension. This does either of two things depending on how the auto-grow settings are configured:

  1. Many small extensions will result in the file fragmentation, as well as minor pauses during the day while the file is extended.
  2. A larger file extension will result in the slowing of the SQL response while the host provided initializes more space for the data file and allows SQL to extend the database in to it.

If the size of the database is large enough then SQL would never be required to extend the database.  However, how much is enough to allocate to a SQL database? The process of managing the data file growth of SQL will include regular monitoring to identify by how much the current SQL database is growing over an extended period. With the growth understood then a periodic manual change of the data file size can be scheduled so the data file always has free space.

Consideration should also be given to the fall back auto-grow process. If the pre-grow schedule fails, the auto-grow process will kick in as a backup. Any increase of the data file size for SQL will require the initializing (formatting) of the file space before it is available to SQL. Large increases of the data file size take time because of this.

Solution options:

  1. Regularly monitor free space and manually increase.
  2. Configure an automatic pre-grow process.
  3. Leave on default auto-grow.

With the delay in the increasing of the file size due to the initializing, all the options above will benefit from a policy change on the host to allow for instant file initialization. Let’s tackle that first.

Instant file initialization.

  1. Instant file initialization is granted to the local administrators group by default.
  2. SQL is running under an active directory account or a local machine user account, i.e. non-local admin.
  3. As a machine admin run secpol.msc to start local security policy. Under security settings, navigate to local policies – user rights assignment – perform volume maintenance task. Add the user account running the SQL service. If the SQL service user is already a member of the local administrators group it will already be able to perform fast file initialization. Restart the SQL database engine for the changes to be effective.
  4. Testing indicated a 1GB extension took 11 seconds before enabling the instant file initialization and 0 seconds after.

Configure automatic pre-growth.

This script can be configured to be used in a number of different ways. In its raw form, it can be used interactively against any database providing the variables are correct. Most will want to have this run as a scheduled job configured in SQL Server agent, or if the need arises, it can be configured as a stored procedure and called from within an application.

The script, created by members of the Professional Advantage Dynamics GP team, will check the current free space within the database and issue an alter database statement to set the DB file to be the current size plus the pre-set increase. The script uses variable in the variables section below, and can be run numerous times without issue.

DECLARE @DBSize INTEGER
DECLARE @DBFree INTEGER
DECLARE @DBMin INTEGER
DECLARE @DBInc INTEGER
DECLARE @DBTot INTEGER
DECLARE @SQL VARCHAR(8000)
DECLARE @DBName CHAR(25)
DECLARE @DBFile CHAR(25)

Variables section:

  • DBMin is the minimum free space in MB desired in the database, e.g. 65GB = 65000.
  • DBInc is the value in MB to increase the database size, e.g. 10GB – 10000.
  • DBName is the SQL database name, and is the database this script is run against.
  • DBFile is the database data file name, i.e. select name from sys.database_files where type_desc = ‘ROWS’.

Set @DBMin = 5
Set @DBINC = 2
Set @DBName = ‘PROD’
Set @DBFile = ‘PROD’

What is the current size and free space?

 The below script will show you what the current size of your database is and the current free space in the database.

select size/128 AS ‘Size before increase’, size/128 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128 AS ‘Free Space before increase’, name as ‘Data file name’ FROM sys.database_files where type_desc = ‘ROWS’

The magic appears here…

DECLARE PA_AUTO CURSOR FOR
select
size/128 AS ‘Total Size in MB’,
size/128 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128 AS ‘dbfree’ FROM sys.database_files
where type_desc = ‘ROWS’ OPEN PA_AUTO

FETCH NEXT FROM PA_AUTO
INTO @DBSize,@DBFree

WHILE @@FETCH_STATUS = 0
BEGIN

Check to see if the current free space is less than the desired free space.

select size/128 AS ‘Size before increase’, size/128 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128 AS ‘Free Space before increase’, name as ‘Data file name’ FROM sys.database_files where type_desc = ‘ROWS’

Example of static file growth set at 22MB.

ALTER DATABASE MyDB
MODIFY FILE (NAME = MyDBFile,SIZE = 22);

Add the required preset increase value to the current database size.

SET @DBTot = @DBSize + @DBINC

Make the change to the database file size increasing the free space.

SET @SQL = ‘ALTER DATABASE ‘ + @DBName + ‘ MODIFY FILE (NAME = ‘ + @DBFile + ‘,SIZE = ‘ + CAST(@DBTot as varchar(300)) + ‘);’
exec (@SQL)
Print @SQL
END
FETCH NEXT FROM PA_AUTO
INTO @DBSize,@DBFree END
CLOSE PA_AUTO
DEALLOCATE PA_AUTO

Check the size after the update.

select size/128 AS ‘DB Size after process’,
size/128 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128 AS ‘Free Space after Process’, name as ‘Datafile name’ FROM sys.database_files
where type_desc = ‘ROWS’

Want to find out more about 1Staff Staffing Software?

1Staff is the leading staffing software and recruitment solution that fully leverages the Microsoft Dynamics platform.

Contact Us

Professional Advantage Managed Services

Want to find out more information on how Professional Advantage can help you with your security needs?
Please contact us to start your discussion about how our Managed Services team can help

Find out more