Alter DB modify log file for all DBs

Question

I can run this command to make changes for 1 DB

USE [master]
GO
ALTER DATABASE [DBName] MODIFY FILE ( NAME = N'Name_log', FILEGROWTH = 10000KB )
GO

Is it possible to create a script which changes log file size to let's say 100MB for all DBs in all servers instead of running the above command by logging into each server? We have about 200 servers and close to 3000 DBs.

score 2 · Accepted Answer · edited Apr 13 '17 at 12:43

You can generate a script to do that using dynamic TSQL and then run it

SET NOCOUNT ON;  
SELECT  'USE [MASTER] ' + CHAR(13) + 'ALTER DATABASE [' + d.name + N'] '
    + 'MODIFY FILE (NAME = N''' + mf.name + N''', FILEGROWTH = 10000 KB)'
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM    sys.master_files mf
    JOIN sys.databases d ON mf.database_id = d.database_id
WHERE   d.database_id > 1
    AND d.state_desc <> 'offline'
    AND mf.type_desc = 'LOG';

Original script from another answer here.

score 1 · Answer 2 · answered Nov 09 '15 at 19:45

As another possibility (and a cleaner one, methinks), here's how you do it in Powershell + SMO:

#Load SMO Assembly
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
#Get Initial user db collection
$dbs = (New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server).Databases | Where-Object {$_.IsSystemObject -eq 0}

#update each db setting
foreach($db in $dbs){
    $db.LogFiles[0].GrowthType = 'KB'
    $db.LogFiles[0].Growth = 102400
}

Note, this assumes a single log file per database. You'd have to have an additional loop if you had dbs with multiple log files.

score 0 · Answer 3 · edited Apr 13 '17 at 12:42

dynamic sql is your friend along with PowerShell or SQLCMD.

--:connect yourserverName -- Make sure your have sqlcmd mode enabled in SSMS
set nocount on
declare @sqltext nvarchar(max) = N''

select  @sqltext += N'alter database '+QUOTENAME(db_name(database_id)) + ' modify file (name = N'''+name+''', filegrowth = 10000KB);'+char(10)
                    from sys.master_files
                    where database_id > 4 -- exclude system databases
                    and state_desc = 'ONLINE'
                    and type_desc = 'LOG' -- since we only want to change log files
                                          -- ROWS = data file
print @sqltext 

-- EXEC sp_executesql @sqltext;

I would suggest you to look at the autogrowth events and then adjust with a good enough value for filegrowth.

note: Uncomment the above exec ... to run the actual statement.

Alter DB modify log file for all DBs

3 Answers3