SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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

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

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

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.







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums






SQL Split String Related SQL Server Tutorials

Split String using XML - How to Convert or Split a Delimited String Values to Rows using T-SQL XML Commands
Split String Into Fixed Length Pieces in SQL
Recursive Function Sample - MS SQL Server Recursive T-SQL Split Function
TSQL Character Split Function in SQL Server
Case Sensitive SQL Split Function
SQL Server 2016 Split String Function STRING_SPLIT
SQL Server String Split T-SQL CLR Function Sample
SQL Server CLR Split String Function for 2-Dimensional Array
T-SQL Split User Defined Function


Copyright © 2004 - 2018 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems