数据库文件的增长量一般推荐为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)