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.

Powershell will forever be your friend, if you let it. – Avarkx Nov 06 '15 at 21:10 — Avarkx, Nov 06 '15 at 21:10

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

2

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.

edited Apr 13 '17 at 12:43

Community

1

answered Nov 06 '15 at 22:04

sql_handle

703
5
15

1

Instead of using [ and ], I would suggest to use QUOTENAME - much easier to read and remember ! Also, why you have database_id > 1 - this will change log file size for msdb, tempdb, model as well. Is this what you intend ? – Kin Shah Nov 06 '15 at 22:08
@Kin +1 Correct. Thanks for the feedback. – sql_handle Nov 06 '15 at 22:09
1

One more thing is that you dont actuall need a join to sys.databases. You can use the function db_name(database_id) to get the database name. Much smaller code :-) – Kin Shah Nov 06 '15 at 22:10
As the OP said 'ALL' databases, I assumed system databases too. – sql_handle Nov 06 '15 at 22:11
@Kin +1 Yes, joining sys.databases is not really needed. – sql_handle Nov 06 '15 at 22:12
I dont see a reason for changing log sizes for system databases apart from tempdb. But the size suggested by OP for tempdb will be even less IMHO. – Kin Shah Nov 06 '15 at 22:12
@Kin I tend to advocate using the joins for consistency, since some metadata functions don't obey isolation semantics (see this post). I don't like programming in a model where you say "here it's ok, because you save typing, but over there it's not ok because of x,y,z"... and then have to memorize where x,y,z apply. However I didn't even follow my own advice there - I used DB_ID() instead of joining on name - so do as I say, not as I do. :-) – Aaron Bertrand Nov 09 '15 at 19:56
@Kin you'll find plenty of my older answers here, too, where I use OBJECT_NAME() and other functions that I now avoid, instead of the more tedious joins. When I come across those due to notifications or edit them for other purposes, I tend to clean up the functions and turn them into joins too. – Aaron Bertrand Nov 09 '15 at 19:58
@AaronBertrand I take your word granted :-). I just do not see a need in this case, but I agree what the expert (you) says and will keep that in becoming better day by day ! – Kin Shah Nov 09 '15 at 20:21

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.

Not working. By running the above command, I do get the message "Query ran successfully, DB altered" but I don't see any change when I go into DB properties. It still says Auto-growth to 10%. — Syed, Nov 06 '15 at 22:17
You have to remove -- from the exec sp_executesql. I have made it deliberately not to run and just to print. Once you are happy with the output, uncomment the last exec and it will run. — Kin Shah, Nov 06 '15 at 22:21

Alter DB modify log file for all DBs

3 Answers3