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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy