2

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 6 '15 at 21:10
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.

  • 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 6 '15 at 22:08
  • @Kin +1 Correct. Thanks for the feedback. – sql_handle Nov 6 '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 6 '15 at 22:10
  • As the OP said 'ALL' databases, I assumed system databases too. – sql_handle Nov 6 '15 at 22:11
  • @Kin +1 Yes, joining sys.databases is not really needed. – sql_handle Nov 6 '15 at 22:12
1

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.

0

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 6 '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 6 '15 at 22:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.