Prevent File Growth Database Property Changes using SQL Server DDL Trigger

SQL Server database administrators can prevent for example developers to change file growth property of a database using server based DDL trigger which SQL source codes are shared in this SQL Server tutorial.

File growth property is an important database variable to be kept under control since it affects performance when the database tries to extend additional disk space from the operating system.
Of course, if all spaces of a data file of a database is filled out, then users won't be able to work on the database until additional space is added to data files.
In short, controlling file growth property especially on production databases could be considered one of the most important settings for database and SQL Server administrators.

In this SQL tutorial, I'ld like to share source codes of a SQL Server DDL trigger which prevents changes on FILEGROWTH option of any database on a SQL Server instance.

First, let's start by updating filegrowth property of a database using following ALTER DATABASE SQL command.


USE [master]

GO

ALTER DATABASE [kodyaz] MODIFY FILE ( NAME = N'kodyaz', FILEGROWTH = 0)

GO

Code

As SQL developers can see, the database filegrowth property can be altered by executing above SQL command

Using SQL Server Management Studio, it is also possible to change configuration of file growth on each database level as follows.
Launch SQL Server Management Studio (SSMS)
On Object Explorer windows, drill down Databases node
Using context menu which is displayed by right-click on the database, display Properties.

display database properties using SQL Server Management Studio

On Files tab, you will see Database files table.
On Autogrowth / Maxsize column, click on "..." button to display configuration screen.

SQL Server database file autogrowth property

Database administrators can enable Authogrowth or disable Authogrowth property for related database. Or File Growth can be configured as a percentage or in fixed size sizes. Additionally if desired a maximum file size can be configured for each database file on SSMS.

SQL Server database file auto-growth settings

Of course it is possible to prevent changes on autogrowth setting using a SQL Server DDL trigger on instance level. Please execute following SQL code.


Create Trigger prevent_filegrowth

ON ALL SERVER

FOR ALTER_DATABASE

AS



declare @SqlCommand nvarchar(max)

set @SqlCommand = ( SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') );



if( isnull(charindex('FILEGROWTH', @SqlCommand), 0) > 0 )

begin

RAISERROR ('FILEGROWTH property cannot be altered', 16, 1)

ROLLBACK

end

GO

Code

If you are a SQL Server programmer new to DDL triggers, I can simply explain it as follows:
Different from triggers (DML triggers) running for database tables when an INSERT, UPDATE or DELETE statement is executed on, DDL triggers are code blocks executed when a data definition language (DDL) command is executed like creation of a database table, or when a new database is created for example.
DDL commands and DDL triggers are managed either in database level or in server level.

Transact-SQL developers can see that prevent_filegrowth trigger is defined on SQL Server instance level with "ON ALL SERVER" clause. Also this DDL trigger is executed when an "ALTER DATABASE" command is executed as defined in "FOR ALTER_DATABASE" clause.

Of course since we aren't interested in all DDL commands managed by ALTER DATABASE syntax, I decided to fetch the SQL command first. Then check if the "FILEGROWTH" option is used within the SQL command. If file growth option is in the ALTER DATABASE command triggering this DDL trigger, I raise an SQL error and rollback the statement and exit.

Let's see how this SQL Server server level DDL trigger shows its effects

On a SQL Server Management Studio, when you try to change any of the File Growth options for a database, the SSMS will show following SQL exception

SQL Server DDL trigger prevents database autogrowth update

If the SQL programmer executes following SQL command on SQL Query Editor windows,


USE [master]

GO

ALTER DATABASE [kodyaz] MODIFY FILE ( NAME = N'kodyaz', FILEGROWTH = 10%)

GO

Code

The developer will experience the below error message:

Msg 50000, Level 16, State 1, Procedure prevent_filegrowth, Line 14 [Batch Start Line 2]
FILEGROWTH property cannot be altered
Msg 3609, Level 16, State 2, Line 3
The transaction ended in the trigger. The batch has been aborted.

And the changes will not be applied on that database settings

I hope this SQL tutorial will be useful to understand how a SQL Server DDL Trigger works and especially sample DDL trigger prevent_filegrowth will enable Server administrators or database administrators to control database properties like file growth options, etc.