June 9, 2015 at 11:57 am
Comments posted to this topic are about the item Adapatative Files Autogrow
July 8, 2015 at 6:26 am
Thanks for the script.
January 7, 2016 at 6:30 am
This is a re-post but thanks anyway.
January 8, 2016 at 2:34 am
Hi, wonderful script. There have been a few issues when I tried to implement it in my server farm.
- read-only databases
- case-sensitive collation on the instance
- databases which have the max_size set and the resulting filegrowth would be exceeding that limit
the core of the script should be
DECLARE @sql VARCHAR(8000)
SET @sql=' USE [?]
SELECT ''?'' [Dbname]
,[name] [Filename]
,type_desc [Type]
,physical_name [FilePath]
,CONVERT(INT,/128.0) [TotalSize_MB]
,CONVERT(INT,FILEPROPERTY(name, ''SpaceUsed''))/128.0 AS [Space_Used_MB]
,CASE is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR(5),growth)
ELSE CONVERT(VARCHAR(20),(growth/128))
END [Autogrow_Value]
,CASE is_percent_growth
WHEN 1 THEN ''Pct''
ELSE ''MB''
END [Unit]
,CASE max_size
WHEN -1 THEN CASE growth
WHEN 0 THEN CONVERT(VARCHAR(30),''Restricted'')
ELSE CONVERT(VARCHAR(30),''Unlimited'') END
ELSE CONVERT(VARCHAR(25),max_size/128)
END [Max_Size]
FROM [?].sys.database_files'
-- Create temp table to store Files informations
IF EXISTS(SELECT 1 FROM tempdb..sysobjects WHERE name='##AutogrowthDetails')
DROP TABLE ##AutogrowthDetails
CREATE TABLE ##AutogrowthDetails (
Dbname VARCHAR(128)
,Filename VARCHAR(128)
,Type VARCHAR(10)
,Filepath VARCHAR(2000)
,TotalSize_MB INT
,Space_Used_MB INT
,Autogrow_Value VARCHAR(15)
,Unit VARCHAR(15)
,Max_Size VARCHAR(30)
)
INSERT INTO ##AutogrowthDetails EXEC sp_MSforeachdb @sql
DECLARE @dbname varchar(8000)
DECLARE @file varchar(8000)
DECLARE @filename varchar(8000)
DECLARE @type varchar(20)
DECLARE @totalsizemb int
DECLARE @autogrowthvalue varchar(20)
DECLARE @max_size varchar(256)
DECLARE @filegrowth_target varchar(20)
DECLARE @sql2 varchar(8000)
-- Set file autogrow value depending on thresholds
DECLARE SetAutogrowthSize cursor for
SELECT Dbname, Filename, Type, TotalSize_MB, Autogrow_Value, Max_Size
FROM ##AutogrowthDetails
WHERE Dbname not in ('master','msdb','tempdb','model')
AND Dbname IN (select name from sys.databases where state_desc = 'ONLINE')
AND Dbname not in (select name from sys.databases where is_read_only = 1)
AND Autogrow_Value <> 0
AND (
(
(TotalSize_MB < @datafilesize_lowerlimit AND Autogrow_Value < @datafile_growth_target_lower and Type = 'ROWS')
OR
((TotalSize_MB BETWEEN @datafilesize_lowerlimit AND @datafilesize_upperlimit) AND Autogrow_Value < @datafile_growth_target_medium AND Type = 'ROWS')
OR
(TotalSize_MB > @datafilesize_upperlimit AND Autogrow_Value < @datafile_growth_target_upper AND Type = 'ROWS')
)
OR
(
(TotalSize_MB < @logfilesize_lowerlimit AND Autogrow_Value < @logfile_growth_target_lower and Type = 'LOG')
OR
((TotalSize_MB BETWEEN @logfilesize_lowerlimit AND @logfilesize_upperlimit) AND Autogrow_Value < @logfile_growth_target_medium AND Type = 'LOG')
OR
(TotalSize_MB > @logfilesize_upperlimit AND Autogrow_Value < @logfile_growth_target_upper AND Type = 'LOG')
)
)
ORDER BY Dbname, Filename
OPEN SetAutogrowthSize
FETCH NEXT FROM SetAutogrowthSize INTO @dbname, @file, @type, @totalsizemb, @autogrowthvalue, @max_size
WHILE @@FETCH_STATUS = 0
BEGIN
IF @type = 'ROWS' AND (@totalsizemb < @datafilesize_lowerlimit AND @autogrowthvalue < @datafile_growth_target_lower)
SET @filegrowth_target = @datafile_growth_target_lower
IF @type = 'ROWS' AND ((@totalsizemb between @datafilesize_lowerlimit AND @datafilesize_upperlimit) AND @autogrowthvalue < @datafile_growth_target_medium)
SET @filegrowth_target = @datafile_growth_target_medium
IF @type = 'ROWS' AND ((@totalsizemb = @datafilesize_upperlimit OR @totalsizemb > @datafilesize_upperlimit) AND @autogrowthvalue < @datafile_growth_target_upper)
SET @filegrowth_target = @datafile_growth_target_upper
IF @type = 'LOG' AND (@totalsizemb < @logfilesize_lowerlimit AND @autogrowthvalue < @logfile_growth_target_lower)
SET @filegrowth_target = @logfile_growth_target_lower
IF @type = 'LOG' AND ((@totalsizemb between @logfilesize_lowerlimit AND @logfilesize_upperlimit) AND @autogrowthvalue < @logfile_growth_target_medium)
SET @filegrowth_target = @logfile_growth_target_medium
IF @type = 'LOG' AND ((@totalsizemb = @logfilesize_upperlimit OR @totalsizemb > @logfilesize_upperlimit) AND @autogrowthvalue < @logfile_growth_target_upper)
SET @filegrowth_target = @logfile_growth_target_upper
SET @sql2 = 'ALTER DATABASE ['+ @dbname + '] MODIFY FILE (NAME = '''+@file+''', FILEGROWTH =' +@filegrowth_target + ')'
Print '********************************************************************************************************************************************'
Print '| Database Name: ' + @dbname + ' | Logical File Name: ' + @file + ' | File Type: ' + @type + ' | Current Size: ' + convert(varchar(20), @totalsizemb) + ' MB | Current Growth increment: ' + @autogrowthvalue
IF @max_size <> 'Unlimited'
BEGIN
IF @max_size < @filegrowth_target
BEGIN
Print '| Max size detected as ' + @max_size + 'MB, can''t set FILEGROWTH properly'
SET @sql2 = ''
END
ELSE
BEGIN
Print '| Process will update filegrowth size from ' + convert(varchar(20), @autogrowthvalue) + ' to ' + convert(varchar(20), @filegrowth_target) + ' MB'
Print '| Executing following ALTER command: '
Print '| '+ @sql2
END
END
Print '********************************************************************************************************************************************'
Print ' '
exec (@sql2)
FETCH NEXT FROM SetAutogrowthSize INTO @dbname, @file, @type, @totalsizemb, @autogrowthvalue, @max_size
END
CLOSE SetAutogrowthSize
DEALLOCATE SetAutogrowthSize
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply