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:
- Many small extensions will result in the file fragmentation, as well as minor pauses during the day while the file is extended.
- A larger file extension will result in the slowing of the SQL response while the host provided initialises 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 initialising (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:
- Regularly monitor free space and manually increase.
- Configure an automatic pre-grow process.
- Leave on default auto-grow.
With the delay in the increasing of the file size due to the initialising, all the options above will benefit from a policy change on the host to allow for instant file initialisation. Let's tackle that first.
Instant file initialisation.
- Instant file initialisation is granted to the local administrators group by default.
- SQL is running under an active directory account or a local machine user account, i.e. non-local admin.
- 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 initialisation. Restart the SQL database engine for the changes to be effective.
- Testing indicated a 1GB extension took 11 seconds before enabling the instant file initialisation 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 below 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 below 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?
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.
IF (@DBFree < @DBMin)
BEGIN
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'
