autogrowth setting

  • plsss give me query or script to find autogrowth setting of all databases in instance

  • SELECT * FROM sys.master_files

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • In growth column it is showing different values like 128 for 1 mb,0 for none.can u pls give me script through which i will get exact information like by 1 mb,unrestricted growth

  • Look in Books Online (the SQL help documentation) on sys.master_files or sys.database_files for what the values mean.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • select DB_NAME(mf.database_id) database_name

    , mf.name logical_name

    , CONVERT (DECIMAL (20,2) , (CONVERT(DECIMAL, size)/128)) [file_size_MB]

    , CASE mf.is_percent_growth

    WHEN 1 THEN 'Yes'

    ELSE 'No'

    END AS [is_percent_growth]

    , CASE mf.is_percent_growth

    WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'

    WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'

    END AS [growth_in_increment_of]

    , CASE mf.is_percent_growth

    WHEN 1 THEN

    CONVERT(DECIMAL(20,2), (((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)

    WHEN 0 THEN

    CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL, growth)/128))

    END AS [next_auto_growth_size_MB]

    , CASE mf.max_size

    WHEN 0 THEN 'No growth is allowed'

    WHEN -1 THEN 'File will grow until the disk is full'

    ELSE CONVERT(VARCHAR, mf.max_size)

    END AS [max_size]

    , physical_name

    from sys.master_files mf

    complete solution

Viewing 6 posts - 1 through 5 (of 5 total)

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