Adapatative Files Autogrow

  • Comments posted to this topic are about the item Adapatative Files Autogrow

  • Thanks for the script.

  • This is a re-post but thanks anyway.

  • 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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply