Technical Article

Get Rid of All Those Pesky 1 MB File Growth DB Files

,

You have 50 user databases on your server. Model has been left at the default file growth of 1 MB for the data file and so all (or almost all) of these databases have a data file growth set at 1 MB for file growth. Use this to set them all to 10%, at least. Later you can go about tweaking them individually. 


Note: this is to make sure you only get the online databases: AND DATABASEPROPERTYEX(''?'', ''Collation'') IS NOT NULL

Note also this only works on the first data file of each database. I am assuming if you have databases with multiple files on them, whoever created the second data file new what they were doing. 

sp_MsForEachDB '
USE [?];
DECLARE @DBNAME VARCHAR(128)
SET @DBNAME = DB_NAME()
DECLARE @FLNAME VARCHAR(128)
SET @FLNAME = (select [name]
                from sys.database_files
                where growth = 128
                  and file_id = 1)
DECLARE @SQL_STRING NVARCHAR(2000)                  
IF EXISTS (select *
            from sys.database_files
            where growth = 128
              and file_id = 1)
    AND DATABASEPROPERTYEX(''?'', ''Collation'') IS NOT NULL
    AND DB_NAME() NOT IN (''master'',''model'',''tempdb'',''msdb'')             
   BEGIN
     SELECT ''Database: '' + @DBNAME + ''----- FileName: '' + @FLNAME
     SET @SQL_STRING =  ''ALTER DATABASE ['' +   @DBNAME  +
                    ''] MODIFY FILE ( NAME = '' + @FLNAME + '', FILEGROWTH = 10%)''
     --PRINT @SQL_STRING
     EXECUTE sp_executesql @SQL_STRING              
   END '

Rate

4 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (2)

You rated this post out of 5. Change rating