SQL Server: 如何快速将多个数据库文件的增长改为50MB

数据库文件的增长量一般推荐为50~150MB， 太大太小都会影响性能。 百分比则可能增长缓慢或者浪费磁盘空间甚至造成磁盘空间不够用。 

那如何快速为多个数据库改增长量呢？

SET NOCOUNT ON
DECLARE @i INT,@iMax INT,@dbName NVARCHAR(200), @sqlTotal NVARCHAR(MAX),@sqlSingle NVARCHAR(MAX),@sql NVARCHAR(MAX)
DECLARE @t TABLE (rowNum INT IDENTITY(1,1), dbName NVARCHAR(200))
INSERT INTO @t (dbName)
SELECT d.name FROM sys.databases AS d WHERE d.name NOT IN  ('master','tempdb','model','msdb')
SELECT @i=1,@iMax=MAX(rowNum),@sqlTotal='',@sqlSingle='' FROM @t

WHILE @i<=@iMax
BEGIN
	SELECT @dbName=dbName FROM @t WHERE rowNum=@i
	SET @sql='use '+@dbName+' SET @sqlSingle= (SELECT ''ALTER DATABASE ['+@dbName+'] MODIFY FILE ( NAME = N{''+s.name+''}, FILEGROWTH = 51200KB );'' FROM sys.sysfiles s FOR XML PATH('''') )'
	EXEC sys.sp_executesql @sql, N'@sqlSingle nvarchar(max) out', @sqlSingle OUT
	SET @sqlTotal=@sqlTotal+@sqlSingle
	SET @i=@i+1
END
SET @sqlTotal=REPLACE(REPLACE( REPLACE(@sqlTotal,';','
'),'{',''''),'}','''')
PRINT @sqlTotal
EXEC (@sqlTotal)