CODEWITHSUNDEEP
sql-serverssms
masoud
masoud

Reputation: 11

How can I disable autogrowth in SQL Server wide

I have a database server that some databases with restricted users are in use in the database. I need to restrict users to can't change .MDF and .LDF autogrowth settings. Please guide me to restrict the users.

I think there is two way to get this access:

  1. Disable autogrowth in databases
  2. Limit the maximum size of MDF and LDF

But I couldn't find any option in Management Studio to do them server wide and also get access from users.

Thanks.

Upvotes: 1

Views: 5633

Answers (1)

Eralper
Eralper

Reputation: 6612

you can execute following ALTER DATABASE command which sets auto growth option to off for all databases using undocumented stored procedure sp_Msforeachdb

for single database (Parallel Data Warehouse instances only)

ALTER DATABASE [database_name] SET AUTOGROW = OFF

for all databases

EXEC sp_Msforeachdb "ALTER DATABASE [?] SET AUTOGROW = OFF"

Although this is not a server variable or instance settings, it might help you ease your task for updating all databases on the SQL Server instance

By excluding system databases and for all other databases, following T-SQL can be executed to get list of all database files and output commands prepared can be executed

select 
    'ALTER DATABASE [' + db_name(database_id) + '] MODIFY FILE ( NAME = N''' + name + ''', FILEGROWTH = 0)'
from sys.master_files
where database_id > 4

enter image description here

To prevent data files' autogrow property to be changed, I prepared below SQL Server DDL trigger once I used a DDL trigger for logging DROP table statements. Following trigger will also prevent you to change this property, so if you need to update this property, you have to drop this trigger first.

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

For more on DDL Triggers, please refer to Microsoft Docs

Upvotes: 1

Related Questions