SQL Server 批量修改使用者資料庫自動成長設定：資料檔 200MB、記錄檔 400MB

預設自動成長 1MB/10% 易造成頻繁擴展碎片化與效能抖動。批量調整所有使用者資料庫：資料檔固定 200MB、記錄檔固定 400MB 成長增量。

資料檔調整預存程序

CREATE PROC pr_modify_file_add @in_name NVARCHAR(256) AS
DECLARE @d_name VARCHAR(20) = @in_name;
EXEC ('ALTER DATABASE ' + @d_name + ' MODIFY FILE (NAME=' + @d_name + ', FILEGROWTH=200MB)');

-- 游標遍歷所有使用者資料庫
DECLARE @databasename NVARCHAR(256);
DECLARE cur CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master','tempdb','model','msdb','reportserver','ReportServerTempDB');
OPEN cur;
FETCH NEXT FROM cur INTO @databasename;
WHILE @@FETCH_STATUS = 0 BEGIN
    EXEC pr_modify_file_add @databasename;
    FETCH NEXT FROM cur INTO @databasename;
END
CLOSE cur; DEALLOCATE cur;

記錄檔調整預存程序

CREATE PROC pr_modify_file_add_log @in_name NVARCHAR(256), @dlog NVARCHAR(256) AS
DECLARE @d_name NVARCHAR(20) = @in_name;
EXEC ('ALTER DATABASE ' + @d_name + ' MODIFY FILE (NAME=' + @dlog + ', FILEGROWTH=400MB)');

-- 動態取得每個資料庫的記錄檔邏輯名稱
DECLARE @databasename NVARCHAR(256), @string NVARCHAR(256), @re NVARCHAR(256);
DECLARE cur CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master','tempdb','model','msdb','reportserver','ReportServerTempDB');
OPEN cur;
FETCH NEXT FROM cur INTO @databasename;
WHILE @@FETCH_STATUS = 0 BEGIN
    SET @string = 'SELECT @a=name FROM ' + @databasename + '.sys.database_files WHERE type=1';
    EXEC sp_executesql @string, N'@a NVARCHAR(200) OUTPUT', @re OUTPUT;
    EXEC pr_modify_file_add_log @databasename, @re;
    FETCH NEXT FROM cur INTO @databasename;
END
CLOSE cur; DEALLOCATE cur;

執行建議

• 執行前備份 master 資料庫。
• 確認磁碟剩餘空間足以支撐成長增量。
• 記錄檔邏輯名稱不固定（如 dbname_log、dbname_Log），動態查詢 sys.database_files 為最佳做法。
• 生產環境建議配合 MAXSIZE 設定上限，防止失控佔滿磁碟。